Buttons Visible/Hidden

[BENSON]

I created a button that when pressed runs a simple macro to copy and paste a colum of data to a new location. I wanted the user to be only able to press the button once per day ,so I added the following code to hide the button once pressed. Howerver I need help on the code for the button to reapear when the worksheet is opened the next day .I think it should be an open event code but not to sure how to write it.I would be thankful for any help.

[VBA]
ActiveSheet.Buttons(Application.Caller).Visible = False
[/VBA]

the entire macro code is as follows:
[VBA]
Sub Button4_Click()
'
' Button4_Click Macro
' Macro recorded 2006/08/09 by Reception
'
' ActiveSheet.Buttons(Application.Caller).Visible = True
ActiveWindow.SmallScroll ToRight:=13
Range("V5:V240").Select
Selection.Copy
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C5").Select
ActiveSheet.Paste
Range("D5:Q240").Select
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("D5:Q240,V5:W240").Select
Range("V5").Activate
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearComments
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Buttons(Application.Caller).Visible = False
Range("D5").Select

End Sub
[/VBA]

[Charlize]

A possible idea. Sheet where you put the date when someone pushes the button. If worksheet opens, check for date. If date is equal then disable button or messagebox to inform user. Instead of sheet maybe documentvariable.

Charlize

[Bob Phillips]

You need to know the name of the button, and restore it like so

[vba]

Worksheets("Sheet1").Buttons("Button 1").Visible = False
[/vba]

[Charlize]

This is what I've got in mind. Perhaps usefull for you. Watch D1.

Charlize

[BENSON]

Thanks for pointers so for I tried your code XLD but got error message "Unable To Get Buttons Property of Worksheet Class"

[Charlize]

Slightly modified version to hide button based on date in D1.

Charlize

[Bob Phillips]

Thanks for pointers so for I tried your code XLD but got error message "Unable To Get Buttons Property of Worksheet Class"

Are your buttons perchance from the control toolbox?

[malik641]

Hey BENSON,

Quick info about your code, you can achieve the same thing by eliminating all the "scrolling" from you code, like this:

[VBA]Sub Button4_Click()
'
' Button4_Click Macro
' Macro recorded 2006/08/09 by Reception
'
Range("V5:V240").Copy Range("C5")

Range("D5:Q240,V5:W240").Select
Range("V5").Activate ' Not sure why this is here
Application.CutCopyMode = False
Selection.ClearContents
Selection.ClearComments
End Sub[/VBA]

Also, you could just write to the registry to keep track of the button's daily use.

Check out Malcom's code here.

HTH

[BENSON]

Many thanks Malik ,for the abreviated code I am learnig alot form this forum.