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

Question

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

I wonder why you have so many buttons to call the same macro. You can add events to the worksheet such that upon selecting a cell, check it's column, and if col B then you can move a button to a particular cell and make visible. Selecting other cells will hide the button. Also Unlock cells that you allow staff to change then protect the sheet like Activesheet.Protect UserInterfaceOnly:=True — PatricK

Carl Colijn Carl Colijn · Accepted Answer · 2016-01-24T14:59:29

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.

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

2 Answers