Button disable

C

Charles

Hi
I have a button which runs a macro which dose the month end stuff. How can I
prevent the operator from pressing it a second time in a session.
Thanks
Charles
 
D

Dave Peterson

Maybe you could create a log worksheet (hide it if you want).

Then plop in the year/month in one of the cells after your code does what it
needs to do.

But change the beginning of the code to check for that value first. If the code
finds it, a little error/warning message and dump them out (or let them override
it???).

I created a worksheet named Log.

I put "Year/Month" in A1
I put "UserName" in B1
I put "Date/Time" in C1

Then I used this as a shell

Option Explicit
Sub testme()

Dim LogRng As Range
Dim okToContinue As Boolean
Dim res As Variant
Dim Ans As Long

With Worksheets("Log")
Set LogRng = .Range("a:a")
res = Application.Match(Format(Date, "yyyy_mm"), LogRng, 0)
If IsError(res) Then
'not found
okToContinue = True
Else
Ans = MsgBox(Prompt:="Again?", Buttons:=vbYesNo)
If Ans = vbYes Then
okToContinue = True
End If
End If

If okToContinue = False Then
Exit Sub
End If
End With

'call YourMacroHere

'add it back to the log (may be a duplicate!)

With Worksheets("log")
With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = Format(Date, "yyyy_mm")
.Offset(0, 1).Value = Application.UserName
With .Offset(0, 2)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With
'make sure you save the updated version of the log
.Parent.Save
End With

End Sub

There are other options. If it's just you running the code, you could add
something to the windows registry. If it's others (and you don't want the
original file changed), you could put the log in a separate workbook--or just
create a text file.

Kind'a depends.
 
D

Dave Peterson

And if you really meant to disable it for just that session, you could disable
that button at the end of your code.

If it's a commandbutton from the control toolbox toolbar placed on a worksheet:

Option Explicit
Private Sub CommandButton1_Click()
'your code
Me.CommandButton1.Enabled = False
End Sub

If it's a button from the forms toolbar that has a macro assigned to it:

Option Explicit
Sub testme()
Dim myBTN As Button
Set myBTN = ActiveSheet.Buttons(Application.Caller)
'your code
myBTN.Enabled = False
End Sub


But opening and closing the workbook (or re-enabling the button by a savy user)
will allow the code to run again.
 
C

Charles

Thanks Dave, just what i need.
Charles
Dave Peterson said:
And if you really meant to disable it for just that session, you could disable
that button at the end of your code.

If it's a commandbutton from the control toolbox toolbar placed on a worksheet:

Option Explicit
Private Sub CommandButton1_Click()
'your code
Me.CommandButton1.Enabled = False
End Sub

If it's a button from the forms toolbar that has a macro assigned to it:

Option Explicit
Sub testme()
Dim myBTN As Button
Set myBTN = ActiveSheet.Buttons(Application.Caller)
'your code
myBTN.Enabled = False
End Sub


But opening and closing the workbook (or re-enabling the button by a savy user)
will allow the code to run again.
Click to expand...
 
C

Charles

Hi again
Enable=false works fine but stays disabled after saving and re-opening a
worksheet. The macro prepares the worksheet for the next month and has to be
saved after themacro has run.
Is there some where the button could be enabled on loading a worksheet.

Charles
 
D

Dave Peterson

You could use a macro in a General module:

Option Explicit
sub auto_open()
worksheets("sheet99").commandbutton1.enabled = true
'or
worksheets("sheet99").buttons("yourbuttonnamehere").enabled = true
end sub

The first is for the commandbutton from the control toolbox toolbar. The second
is for a button from the froms toolbar.

Remember to change the sheet name and button names.
 
Top