CODEWITHSUNDEEP
excelvbauserform
Drag00n
Drag00n

Reputation: 27

many buttons (userform) control a single macro opens a different userforms depending on selection

I have created small excel form for updating a database. works great, though staff are doing odd things and have to replace the excel weekly with a clean version. So I am thinking of creating userforms that update the excel sheet(DutySelection). I have many buttons (userform) A4:A31 that will control a single macro which opens 3 different userforms depending on B4:B31 dropdown list selection

Currently My code only works from B4 no matter which button i click. EG: B4 selection Start, the Start form opens. B6 selection Finish, the Start form opens

Sub Duty()

    If Sheets("DutySelection").Range("B4,B31") = "Start" Then
        frmStart.Show

    ElseIf Sheets("DutySelection").Range("B4,B31") = "Duty Type" Then
        ReportUpdate.Show

    Else: Sheets("DutySelection").Range("B4,B31") = "Finish" 'Then
        frmFinish.Show

    End If
End Sub

I am thinking that i am missing a line or two but just can not find what i am needing online

Upvotes: 0

Views: 176

Answers (2)

Carl Colijn
Carl Colijn

Reputation: 1617

Sheet.Range("B4,B31") doesn't return what you think it does: it returns a composite range consisting of 2 areas, area 1 being cell B4 and area 2 being cell B31. I.e., the same as you would get when you select cell B4, then Ctrl-Clicked cell B31.

I think you meant "B4:B31", but this also returns something else: an array filled with (the values of) all cells in the range B4 to B31. You cannot compare it with a text string just like that.

What you do want here is to loop through all cells between B4 and B31, then compare their values to the texts you're interested in.

Another issue is that your code only ever acts upon the first text it matches. So, if cell B4 contains "Start", then there's no way the ElseIf will ever be evaluated, not even if cell B5 contains "Duty Type". The best way to deal with this depends on how you get those texts in column B on your sheet.

Upvotes: 1

vatbub
vatbub

Reputation: 3109

If I understood you correctly, you have a button in each row next to column B and clicking it invokes the action selected in column B in the corresponsing row, right?

In that case I would suggest that you place 3 buttons next to each other that invoke 3 different macros.

Greetings, vat

Upvotes: 0

Related Questions