PDA

View Full Version : Buttons Visible/Hidden



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

Charlize
01-11-2007, 02:07 AM
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
01-11-2007, 02:53 AM
You need to know the name of the button, and restore it like so



Worksheets("Sheet1").Buttons("Button 1").Visible = False

Charlize
01-11-2007, 03:14 AM
This is what I've got in mind. Perhaps usefull for you. Watch D1.

Charlize

BENSON
01-11-2007, 07:03 AM
Thanks for pointers so for I tried your code XLD but got error message "Unable To Get Buttons Property of Worksheet Class"

Charlize
01-11-2007, 07:31 AM
Slightly modified version to hide button based on date in D1.

Charlize

Bob Phillips
01-11-2007, 09:27 AM
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
01-11-2007, 10:19 AM
Hey BENSON,

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

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

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

Check out Malcom's code here (http://vbaexpress.com/kb/getarticle.php?kb_id=208).

HTH

BENSON
01-12-2007, 04:14 AM
Many thanks Malik ,for the abreviated code I am learnig alot form this forum.