Excel VBA code to disable four buttons in order of priority
There are four buttons to click to take four sample weights. The buttons are B1,B2,B3 and B4. B1 must be clicked first, followed by B2, then B3 and lastly B4 in that order of priority. I want to write an excel VBA code for below senario: (1)B2, B3 and B4 must be disabled until B1 is clicked and (2)B3 and B4 must be disabled until B2 is clicked and (3)B4 must be disabled until B3 is clicked
Any idea would help.
One approach is to enable/disable the button - you add 4 buttons, assign the event
Button4.Enabled. Please note that Enabled does not turn off the events assigned to the button. Also
.Enabledwill not change the color, it's better to use your own handler for that. What I would do is to add the 4 buttons, leave their names as they are (Button 1 to Button 4). Then use this somewhat generic solution:
Option Explicit Public Sub Button1_click() If Sheet1.Buttons(Application.Caller).enabled = False Then Exit Sub Dim btn As Button,arr As Variant,s% arr = Array(2, 3, 4, 1) s = Int(Split(Application.Caller, " ")(1)) - 1 For Each btn In Sheet1.Buttons changeButtonStatus btn, btn.Name = "Button " & arr(s) Next btn End Sub Private Sub changeButtonStatus(ByVal btn As Button, ByVal enabled As Boolean) btn.Font.ColorIndex = IIf(enabled, 1, 15) btn.Enabled = enabled End Sub
What does it do? Creates an array of integers which point to the next button to be enabled. First is Button 2, second is Button 3, third is Button 4, fourth is Button 1. Then checks which button was pressed (Application.Caller), and retrieves the number at the end (by using Split function), and decrease it by one - because the array indexing starts at 0. Then changes the status - if the button is the next one, it enables it, else disables.