Adding data with multiple buttons in VBA

Question

I'm trying to use buttons as the only input into a worksheet database.

6 buttons

Only buttons 1,2 & 3 will add data to my worksheet via the code below

'Name for Button 1 = B_1 
Private Sub B_1_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")

'find first empty row in database
iRow = ws.Range("A:D").Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'copy the data to the database
ws.Cells(iRow, 1).Value = Date
ws.Cells(iRow, 2).Value = Time
'This has to be bacon, tomato or cheese
ws.Cells(iRow, 3).Value = ???????
ws.Cells(iRow, 4).Value = 1

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"

Application.DisplayAlerts = False

ActiveWorkbook.Save

End Sub

IRow, 3 is where I have the problem. I want to store the info for bacon, tomato or cheese first and then when I press 2. The data in the worksheet should look something like this.

Date 12/01/2015

Time 14:20

Food BACON

Quantity 2

I've used a textbox previously as the input.

Can anybody please assist me with this code.

Thanks

If you really must use buttons, a completely unnecessary workaround would be to hide a checkmark behind the button, and on-click select that checkmark and unselect any other ones. Then bring the value of the checkmark down into your quantity on click. At the end of the quantity on click Sub make sure to clear all checkmarks. — Chrismas007
– Chrismas007, Commented Jan 27, 2015 at 15:34
Thank Chrismas007, the issue I have with the dropdown list is that my ingredients list consist of more than 20+ items. The checkmark on the other hand could work. Thanks — Sergeistz
– Sergeistz, Commented Jan 27, 2015 at 15:47

Dick Kusleika · Accepted Answer · 2015-01-27 17:23:56Z

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

Thank you sir my program works like a dream. I still need to optimise the coding but overall its awesome.

Collectives™ on Stack Overflow

Adding data with multiple buttons in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related