If you want to work with only one food at a time, you should use Option Buttons instead of Commandbuttons. Option Buttons are mutually exclusive - if you select Cheese, Bacon is automatically deselected.
If you want to process multiple foods at once, you should use Toggle Buttons. Toggle Buttons are either up or down, but they're not mutually exclusive. They have the added benefit of looking like Command Buttons.
If you want the mutual exclusivity of Option Buttons, but you must have something that looks and works like a Commandbutton, then you have a couple of options, none of them optimal:
- You could use a Tab Strip and hide everything except the tabs.
- You could set a module-level variable that remembers the last button pushed. You would probably want to add code that would change the color of the button so the user knows which they pressed. And if you did that, you probably don't need the module-level variable, you could just read which button had the color.
- You can make Toggle Buttons mutually exclusive through code. I'd personally go with this one so you get the visual effect of the button being pressed.
Here's some code to get you started
Private mbEventsDisabled As Boolean Public Property Let EventsDisabled(ByVal bEventsDisabled As Boolean): mbEventsDisabled = bEventsDisabled: End Property Public Property Get EventsDisabled() As Boolean: EventsDisabled = mbEventsDisabled: End Property Private Sub tgBacon_Click() If Not Me.EventsDisabled Then ClearToggles Me.tgBacon End Sub Private Sub tgCheese_Click() If Not Me.EventsDisabled Then ClearToggles Me.tgCheese End Sub Private Sub tgTomato_Click() If Not Me.EventsDisabled Then ClearToggles Me.tgTomato End Sub Public Sub ClearToggles(tg As ToggleButton) Me.EventsDisabled = True Me.tgBacon.Value = Me.tgBacon.Name = tg.Name Me.tgCheese.Value = Me.tgCheese.Name = tg.Name Me.tgTomato.Value = Me.tgTomato.Name = tg.Name Me.EventsDisabled = False End Sub
If you had more than three toggles, you'd want to refactor the ClearToggles sub to loop instead of calling them out individually.
Public Sub ClearToggles(tg As ToggleButton) Dim ctl As Control Me.EventsDisabled = True For Each ctl In Me.Controls If TypeName(ctl) = "ToggleButton" And ctl.Name <> tg.Name Then ctl.Value = False End If Next ctl Me.EventsDisabled = False End Sub
- Adding data with multiple buttons in VBA
- Add multiple data series to excel chart with VBA
- Convert through vba row with columns of data into column with multiple rows in Excel 2003
- Change data type of column in multiple tables in Access with VBA
- How can I shuffle multiple independent data list with Excel VBA
- Excel VBA 2010 - Command buttons stop working with multiple sheets selected
- How do you use VLOOKUP with a loop in Excel VBA to write data into multiple sheets?
- Create Multiple Data Validation Lists in One Sheet with VBA
- Split Data into Multiple Sheets with VBA
- Excel VBA Dynamic data validation drop downs with multiple criteria ranking
- Application-defined or object-defined error for Type when adding data validation with VBA
- VBA Code to Check one Column Data with Multiple Columns Data?
- Powerpoint VBA addin with multiple buttons
- Excel VBA macro for consolidating data from multiple sheets based on sheet names with specific text
- How to import data from multiple text files on a folder, starting on line 20, while adding the current file name on the first column using excel VBA
- Access VBA - adding row with multiple columns
- Adding a textbox in VBA with multiple properties
- VBA to compare data to itself using multiple criteria and tag items with identifier
- Trouble with VBA to transfer data from multiple workbooks to master workbook
- Split multiple comma separated entries from multiple columns to new rows with unique data in excel macro VBA
- Consolidate Data From Multiple Worksheets And Workbooks With Different Column Headers Into 1 Sheet Using VBA
- VBA Copy and Paste Data with Matching Sheet Name and Multiple Criteria
- VBA : Combine multiple Workbooks(with mutiple Worksheets) into One Workbook with Data One Below The Other
- Adding a new row with data to excel sheet using VBA
- Creating multiple data histories with Excel VBA using LastRow, Time Stamp and Workbook.sheetchange
- How do I assign arrays with multiple data types in VBA and then use the data in functions requiring certain datatypes?
- Data extract from one sheet to another sheet with multiple criteria in excel vba
- Copying data with different columns and sheet name from multiple workbooks - VBA
- Insert brackets in a multiple set of data with vba
- Slicer for multiple pivot with different data source Excel VBA
More Query from same tag
- Replace hyperlink with text from linked document
- How to capture additional line from various worksheets
- 1 MS Access Query to Multiple Excel Files Based on Field Value
- Saving each row of a .xlsx file to .txt
- A function Excel which sends back the date of Monday of the next week
- VBA column doesn’t delete whenever header appears on sheet
- Access INSERT INTO using DateAdd to update field
- Access VBA - Product search fails
- Start VBA macro (Excel) from commandline
- MS Access 2010, Excel 2010, Windows Server 2008 R2 64-bit: SaveAs method of Workbook class failed
- VBA Copying text from one worksheet to active worksheet
- Using a variable or string as a file name for importing data using query tables
- selection of multiple columns with end(xlDown).End(xlUp).Row
- How to get an HTA to restart itself?
- Merge down columns A and B under each headings
- discrete function by using VBA
- Right to left userforms in excel - VBA
- VBA Selenium Chrome Unexpected Alert Error
- Extract All Named Ranges Into A Class
- Excel VBA Reference variable sheet name
- VBA to return RGB color of text
- Excel, simple reverse equation
- Excel macro to create new sheet every n-rows
- Data in Array of earlier pocket is being deleted
- VBA acces auto numbering failure
- Convert word-files to txt files
- What is the problem with the StrComp code below
- MS Access VBA .recordcount returning 0 when records exist, and debug.print returns value
- Compare two column in the same worksheet VBA
- Using drop down list linked to macros