×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Command Button Code Help

Excel Command Button Code Help

Excel Command Button Code Help

(OP)
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.

RE: Excel Command Button Code Help

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

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

RE: Excel Command Button Code Help

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

1) no vba can run.

2) your file never gets written.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

(OP)
VBA is enabled on all 5 computers. We have been using the spreadsheet successfully (most of the time smile) 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.

RE: Excel Command Button Code Help


CODE

‘
   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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

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

RE: Excel Command Button Code Help

(OP)
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

RE: Excel Command Button Code Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

(OP)
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.

RE: Excel Command Button Code Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

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

RE: Excel Command Button Code Help

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. winky smile

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

(OP)
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

RE: Excel Command Button Code Help

Quote:

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

Full??? 2 million+ rows???

Quote:

all of the indents dissapeared

Use TGML tags for CODE.

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Command Button Code Help

(OP)
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.

RE: Excel Command Button Code Help

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

White Paper - PLM and ERP: Their Respective Roles in Modern Manufacturing
Leading manufacturers are aligning their people, processes, and tools from initial product ideation through to field service. They do so by providing access to product and enterprise data in the context of each person’s domain expertise. However, it can be complicated and costly to unite engineering with the factory and supply chain. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close