TorpedoJuice
03-10-2009, 07:08 AM
I have the following code written within a sub to create buttons and assign those same buttons a macro on a given worksheet. The larger picture is that a user selects a day of the week and runs a macro. For each day of the week a variable list of buttons are returned to the screen. If the user selects the wrong day of the week...I want to be able to clear the buttons.
While stepping through the code...each button shows an incremental number (i.e. Button 408, Button 409)...
How do I assign names to the created buttons so that, if necessary, I can clear.
Thanks in advance for any help.
nTextLength = 0
nButtonLeft = 560.75
nButtonTop = 85.75
nButtonWidth = 87.75
nButtonHeight = 26.25
nLenButtonName = 0
cButtonName = ""
cMacroName = ""
For N = 1 To nButtons
Sheets(sFindDataWorksheet).Select
Range("A" & nYesStartRange).Select
Do While N <= nButtons
Sheets(sFindDataWorksheet).Select
Range("A" & nYesStartRange).Select
cButtonName = ActiveCell.Offset(0, 3).Value
nLenButtonName = Len(cButtonName)
cMacroName = ActiveCell.Offset(0, 4).Value
Sheets(sWorksheet).Select
ActiveSheet.Buttons.Add(nButtonLeft, nButtonTop, nButtonWidth, nButtonHeight).Select
With Selection.Characters(Start:=1, Length:=nLenButtonName).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3 'Red
End With
Selection.Characters.Text = Left(cButtonName, 32)
Selection.OnAction = cMacroName
N = N + 1
nYesStartRange = nYesStartRange + 1
nButtonTop = nButtonTop + 30
If nButtonTop >= 300 And nButtonLeft = 560.75 Then
nButtonTop = 85.75
nButtonLeft = 660.75
ElseIf nButtonTop >= 300 And nButtonLeft = 660.75 Then
nButtonTop = 85.75
nButtonLeft = 760.75
End If
Loop
Next
While stepping through the code...each button shows an incremental number (i.e. Button 408, Button 409)...
How do I assign names to the created buttons so that, if necessary, I can clear.
Thanks in advance for any help.
nTextLength = 0
nButtonLeft = 560.75
nButtonTop = 85.75
nButtonWidth = 87.75
nButtonHeight = 26.25
nLenButtonName = 0
cButtonName = ""
cMacroName = ""
For N = 1 To nButtons
Sheets(sFindDataWorksheet).Select
Range("A" & nYesStartRange).Select
Do While N <= nButtons
Sheets(sFindDataWorksheet).Select
Range("A" & nYesStartRange).Select
cButtonName = ActiveCell.Offset(0, 3).Value
nLenButtonName = Len(cButtonName)
cMacroName = ActiveCell.Offset(0, 4).Value
Sheets(sWorksheet).Select
ActiveSheet.Buttons.Add(nButtonLeft, nButtonTop, nButtonWidth, nButtonHeight).Select
With Selection.Characters(Start:=1, Length:=nLenButtonName).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3 'Red
End With
Selection.Characters.Text = Left(cButtonName, 32)
Selection.OnAction = cMacroName
N = N + 1
nYesStartRange = nYesStartRange + 1
nButtonTop = nButtonTop + 30
If nButtonTop >= 300 And nButtonLeft = 560.75 Then
nButtonTop = 85.75
nButtonLeft = 660.75
ElseIf nButtonTop >= 300 And nButtonLeft = 660.75 Then
nButtonTop = 85.75
nButtonLeft = 760.75
End If
Loop
Next