BENSON
01-11-2007, 12:12 AM
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.
ActiveSheet.Buttons(Application.Caller).Visible = False
the entire macro code is as follows:
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
ActiveSheet.Buttons(Application.Caller).Visible = False
the entire macro code is as follows:
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