Excel Command Button Code Help 1

[SteveGregory]

I have the code for 2 buttons that I would like to add a new feature. I would like each button to delete a small txt file and write a new txt file to the same folder that the spreadsheet was launched from.

Here are the files:
1. "Ram Elements IN USE.txt" with one line of text that contains the active cell's contents.
2. "Ram Elements EXIT.txt" with one line of text that contains the active cell's contents.

Here is my current code for Button 1:
Private Sub CommandButton1_Click()
Dim RetVal
ActiveCell = Now() & " " & Environ("username")
' Delete "Ram Elements EXIT.txt" and write new "Ram Elements IN USE.txt"
ActiveWorkbook.Save
ActiveCell.Offset(0, 1).Select
RetVal = Shell("C:\RamElem.cmd", 1)
End Sub

The code for Button 2 is similar:
Private Sub CommandButton2_Click()
ActiveCell = Now() & " " & Environ("username")
' Delete "Ram Elements IN USE.txt" and write new "Ram Elements EXIT.txt"
ActiveCell.Offset(1, -1).Select
ActiveWorkbook.Close SaveChanges:=True
End Sub

As you guessed, I don't have a clue how to make it work other than how to write a comment line. The spreadsheet and the current buttons work well in our environment. Adding and deleting the text files will allow us to see at a glance if anyone and who is using the 1 copy of the program that is licensed to us.

 

[SkipVought]

Hi,

What if the perp 1) never enables macros or 2) never clicks either button?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

The program is set up on 5 computers and I know where they live and the name of their first-born!

 

[SkipVought]

So what! What about either 1) or 2)?

1) no vba can run.

2) your file never gets written.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

VBA is enabled on all 5 computers. We have been using the spreadsheet successfully (most of the time ) to record the user's name, start times, exit times and the next available free usage time. Plus, the first button launches the program. We have been using it for several years.

The spreadsheet file is located in a local Dropbox folder on each computer that gets synced through the web. This allows a remote user working from home to use the program as well. We leave the spreadsheet file open while using the program and then hit the second button after closing the program. The problem comes when the spreadsheet is open and some else tries to open their local version of the spreadsheet. Even if the second person does nothing but view the spreadsheet, realizes that someone is using the program and closes the spreadsheet without saving it, Dropbox decides that the first user needs to save his copy with "conflicted copy" appended to it. This hasn't happened but a few times, but it is something we would like to avoid.

My solution with the simple text files should work, not elegant but simple. We have 2 separate programs owned by Bentley and we have one license for each one. So far one license has worked for us since we are a small firm. Bentley will let us use as many copies as we want and then automatically start billing us for the extra copies. If our workload and the number of engineers start growing, we may decide on our own to increase the number of licenses we want.

A second spreadsheet is used for the other Bentley program.

 

[SkipVought]

‘
   Dim FileNumber, fName As String

   Kill ThisWorkbook.Path & "\EXIT.txt"

   fName = ThisWorkbook.Path & "\USE.txt"

   FileNumber = FreeFile    ' Get unused file number.

   Open fName For Output As #FileNumber    ' Open file

   Write #FileNumber, Now() & " " & Environ("username")

   Close #FileNumber    ' Close file.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

Thanks! My old brain cells are past their limits tonight (11 pm). I'll look at this tomorrow.

 

[SteveGregory]

Skip, Thanks to you, I think I got it working. Plus I added one more line to the EXIT text file with some googling and head scratching and experimenting. If you see anything that might trip me up, let me know.
Steve

Here are the 2 command button scripts:

Private Sub CommandButton1_Click()
'Win XP:RetVal = Shell("C:\Program Files\Bentley\Engineering\RAM Elements\RAMElements.exe", 1)
'Win 7:RetVal = Shell("C:\Program Files\Bentley\Engineering\RAM Elements\RAMElements.exe", 1)
Dim RetVal
ActiveCell = Now() & " " & Environ("username")
ActiveWorkbook.Save
Dim FileNumber, fName As String
Kill ThisWorkbook.Path & "\Ram Elements EXIT.txt"
fName = ThisWorkbook.Path & "\Ram Elements IN USE.txt"
FileNumber = FreeFile ' Get unused file number.
Open fName For Output As #FileNumber ' Open file
Write #FileNumber, Now() & " " & Environ("username")
Close #FileNumber ' Close file.
' Program is now being used and file IN USE is written
ActiveCell.Offset(0, 1).Select
RetVal = Shell("C:\Program Files (x86)\FreeAlarmClock\FreeAlarmClock.exe", 1)
' RetVal = Shell("C:\RamElem.cmd", 1)
'RetVal = Shell("C:\Program Files\Bentley\Engineering\RAM Elements\RAMElements.exe", 1)
End Sub

Private Sub CommandButton2_Click()
ActiveCell = Now() & " " & Environ("username")
' move to column D and select
ActiveCell.Offset(0, 2).Select
Dim FileNumber, fName As String
Kill ThisWorkbook.Path & "\Ram Elements IN USE.txt"
fName = ThisWorkbook.Path & "\Ram Elements EXIT.txt"
FileNumber = FreeFile ' Get unused file number.
Open fName For Output As #FileNumber ' Open file
Write #FileNumber, Now() & " " & Environ("username")
' write second line with text + current cell contents
Write #FileNumber, "Next User Wait Until " & Format(ActiveCell.Value, "medium time")
Close #FileNumber ' Close file.
' Program is now closed and EXIT file is written
ActiveCell.Offset(1, -3).Select
ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[SkipVought]

Using ActiveCell is a bit dicey for me, especially abruptly at the beginninh of a procedure.

It assumes that the cell you expect to be Active, actually is Active.

Otherwise, hope this all works to keep things straight.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

Our spreadsheet serves to keep a database log of our activity, launch the program and protect us from using more than one license at a time. As long as no one clicks on anything except the 2 buttons, the active cell will track across and down through the database just fine and the time calculations that are done inside the sheet will work.

It's not foolproof, but it might have to get pretty elaborate to make it so. Columns A and B are "stamped" by the buttons. Columns C and D are calculated by parsing the stamped values. Some of the calculations are done in C and D and the rest in about 6 columns beyond. See a screen capture upload.

 

[SkipVought]

Well here’s something to think about.

If you use certain Events as your triggers to run your two prcedures, rather than button click events, it might work better. I’d make the log sheet Hidden and use a password to unhide, use the Workbook_Open event to run the first procedure and then the Workbook_BeforeClose event to SAVE and run your exit procedure, so anytime the user closes the workbook this event fires, whic will save and log the exit.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

Thanks, Skip
It looks like I may be working on an enhancement for my next version. Meanwhile, I have to do some real engineering!

 

[SkipVought]

If I get a round TUIT, I’ll post a sample of this process.

Meantime, I’m doing upholstery and drapery enhancements for a relative’s air bnb. A far cry from vba. ;-)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

Skip,
I may have found your round tuit. With a little help from google, I put together some more VBA code. I liked your suggestion to avoid using activecell. It is too easy for someone to click on a stray cell somewhere and make that the activecell. In fact it happened very soon after my latest revision. For some reason, it was not a problem in the past. We were lucky!

So I put all of the time stamped data on a separate sheet "Data" behind the top "public" sheet "Ram Elements". I used a counter stored at cell D1 to keep track of the data row I was writing the time stamps.

On the public sheet, I am also writing the data rows for 4 records including the current one just so that the users can see the last few uses of the program and who is doing it. Finally, I put a warning on the "public" sheet when the data sheet is almost full so I can archive that data and reset the counter.

{all of the indents dissapeared} Take a look:
Private Sub CommandButton1_Click()
Dim RetVal, FirstRow As Integer, DLoop As Integer
FirstRow = Worksheets("Data").Range("D1").Value
Worksheets("Data").Cells(FirstRow, 1).Value = Now() & " " & Environ("username")
'Display 4 rows of "Data" on "Ram Elements" sheet, rows 5-8
For DLoop = 1 To 4
Worksheets("Ram Elements").Cells(DLoop + 4, 1).Value = Worksheets("Data").Cells(FirstRow - 4 + DLoop, 1).Value
Worksheets("Ram Elements").Cells(DLoop + 4, 2).Value = Worksheets("Data").Cells(FirstRow - 4 + DLoop, 2).Value
Next DLoop
ActiveWorkbook.Save
Dim FileNumber, fName As String
Kill ThisWorkbook.Path & "\Ram Elements EXIT.txt"
fName = ThisWorkbook.Path & "\Ram Elements IN USE.txt"
FileNumber = FreeFile ' Get unused file number.
Open fName For Output As #FileNumber ' Open file
Write #FileNumber, Now() & " " & Environ("username")
Close #FileNumber ' Close file.
' Program is now being used and file IN USE is written
' Run Ram Elements program
RetVal = Shell("C:\RamElem.cmd", 1)
' Run Dummy Test program
'RetVal = Shell("C:\Program Files (x86)\FreeAlarmClock\FreeAlarmClock.exe", 1)
End Sub

Private Sub CommandButton2_Click()
Dim FirstRow As Integer
FirstRow = Worksheets("Data").Range("D1").Value
Worksheets("Data").Cells(FirstRow, 2).Value = Now() & " " & Environ("username")
Worksheets("Ram Elements").Cells(8, 2).Value = Worksheets("Data").Cells(FirstRow, 2).Value
Dim FileNumber, fName As String
Kill ThisWorkbook.Path & "\Ram Elements IN USE.txt"
fName = ThisWorkbook.Path & "\Ram Elements EXIT.txt"
FileNumber = FreeFile ' Get unused file number.
Open fName For Output As #FileNumber ' Open file
Write #FileNumber, Now() & " " & Environ("username")
' write second line with text + Column D cell contents
Write #FileNumber, "Next User Wait Until " & Format(Worksheets("Data").Cells(FirstRow, 4).Value, "medium time")
Close #FileNumber ' Close file.
' IN USE file is deleted and EXIT file is written
' Set row for next user
Worksheets("Data").Range("D1").Value = FirstRow + 1
' Save and exit spreadsheet
'ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[SkipVought]

warning on the "public" sheet when the data sheet is almost full...

Full??? 2 million+ rows???

all of the indents dissapeared

Use TGML tags for CODE.

Sub Samp()
    For i = 1 to 5
        Debug.Print i
    Next
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SteveGregory]

Full is about 260 rows. For each row of time stamps (start, stop), I have 8 cells of formulas to parse the data and do time calculations. It would be simple to keep copying those formulas down, but I didn't want the size of the file to grow too large or slow it down.

Are the TGML tags something you add or use while in the Excel VBA code sheet? I was just using spaces to make it more readable.

 

[SkipVought]

It would be simple to keep copying those formulas down, but I didn't want the size of the file to grow too large or slow it down.

You ought never copy formulas into unused rows for any reason, even anticipation of future use!

That is why, in the 2007 version and following, Excel has a feature for tables called Structured Tables, where one of the great features is that as rows are added to these tables, all formulas are copied to the added row(s).

Too large is not in the neighborhood of 260 or 2600 or 26000. I’ve had workbooks containing multiple large tables and lots of formulae that ran just fine. In some cases, I had to turn off automatic calculation and such, but those were in the minority.

TGML is for here. Check the TGML box. Then on the line below, the fifth icon from the right is the Code icon. Select your code in the Reply window and the hit the Code icon. Then Preview your reply. In the Excel VBA Editor I’d recommend always using indented (TAB) structure to identify code groupings like If...End If, For...Next, With... End With, Do...Loop etc.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}