VBA Excel Form with 'modes'

Hello!

I have an Excel vba form with:

• a set of textboxes and comboboxes
• a set of buttons

The values entered in one subset from the textboxes and comboboxes (two of them) should define a 'mode' for the form. Depending of the values of that subset:

• some textboxes and comboboxes need to be enabled or disabled (maybe even hide)
• some buttons need to be enabled or disabled (maybe even hide)
• the tab sequence for textboxes, comboboxes and buttons may be redefined
• a certain textbox, combobox or buton should get the focus.

I already understand I can't do those actions within the code for the AfterUpdate event... but then, how can I implement that?

Thanks a lot!

Hello 6StringJazzer! Do you play guitar? I do! =)

I attached a jpg with an image of the form I want to create.

I want to have 'Form State 1', where only objects 1, 2 and 11 are enabled, and object 1 has the focus.

Once user has entered info on fields 1 and 2, I query a database (fields 1 and 2 form a primary key on a table there). If I get a record, then I go to 'Form State 2' if I don't, then I go to 'Form State 3'.

In 'Form State 2', I populate objects 3, 4, 5, 6, 7 from the results of the SQL Query, and then only objects 3, 4, 5, 6, 7, 8, 9, 10 and 11 are enabled, and object 10 gets the focus.

In 'Form State 3', only objects 3, 4, 5, 6, 7, 8, 10 and 11 are enabled, and object 3 gets the focus.

Button 8 does an INSERT or UPDATE over the SQL table, clears all objects, and sends the Form to 'Form State 1'

Button 9 does a DELETE over the SQL table, clears all objects, and sends the Form to 'Form State 1'

Button 10 clears all objects, and sends the Form to 'Form State 1'

Button 11 clears all objects, sends the Form to 'Form State 1', and exits the User Form.


How can I get this? I was thinking it was going to be possible using code within the AfterUpdate event, but, surprise! it doesn't works that way. =(

Jeff,

... and while I wait for your advice (or someone else's), I'll try to use the Change events as you proposed...

Thanks! =)

I would have 3 subs to transition to each of your states. Here is the example for how to get to State1. You would repeat similar logic for State2 and State3 based on your description

Private Sub State1()

   ComboBox_1.Enabled = True
   ComboBox_1.SetFocus
   TextBox_2.Enabled = True
   ComboBox_3.Enabled = False
   TextBox_4.Enabled = False
   ComboBox_5.Enabled = False
   CheckBox_6.Enabled = False
   TextBox_7.Enabled = False
   CommandButton_8.Enabled = False
   CommandButton_9.Enabled = False
   CommandButton_10.Enabled = False
   CommandButton_11.Enabled = True

End Sub

Private Sub CommandButton_8_Click()

   '  do an INSERT or UPDATE over the SQL table, clears all objects

   State1

End Sub

Private Sub CommandButton_9_Click()

 ' do a DELETE over the SQL table, clears all objects

   State1

End Sub

Private Sub CommandButton_10_Click()

   ' clear all objects

   State1

End Sub

Private Sub CommandButton_11_Click()

   ' clear all objects

   State1

   Me.Hide

End Sub