Userform OK button activate

A

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472
Hi everyone,

This is a problem that I will do my best to describe because I just don't know the right terminology for.

I have a userform with a textbox and a combobox. The user is supposed to type something in the textbox and choose one of the options from the combobox. I want to be able to deactivate the combobox until there is something in the textbox and one of the options brom the combobox are chosen.

It should be something like:

Code: 
If Controls("TextBox1").Text <> "" AND Controls("Combobox").Value <> "" Then
CommandButton1_Click.Activate

I know that this code is completely off, but I hope it will help examplify what I am after.

Any ideas?

AMAS
 
Sort by date Sort by votes
Hi Norie,

The textboxes/combobox combinations allow the user to input text and chose an action. The reason for the large number is that each corresponds to one category (e.g. Monday, Tuesday, etc.).

Because of all the textboxes and comboboxes I have a lot of Change subs. For example, these are the names for the changes subs 1 - 2:
Code: 
Private Sub TextBox1_Change()

Private Sub ComboBox1_Change()
 
Private Sub TextBox2_Change()

Private Sub ComboBox2_Change()

Is there a way to for example combine the textbox change events into one?

AMAS
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
AMAS

Rather than that have you considered another approach - one combobox, one textbox, a command button and a list box.

The user selects from combobox, inputs into textbox and clicks the button.

Then the selection and input are added to the listbox and the combobox and textbox cleared for more input.

Once the user has entered the required data another button is enable.

They click this button and then the code proceeds with the next step, using the data from the listbox rather than the comboboxes/textboxes.
 
Upvote 0
Hi Norie,

I have not tried this approach before. Would you possibly have a worked example or a link to somewhere where I can see how to do this?

AMAS
 
Upvote 0
I don't have a 'worked' example but if you can supply a but more information I could probably knock something up.

For example, what options would be listed in the combobox(s)?

Also, what are you going to do with the data once the user has finished entering it?

That would probably be enough to mock up a simple example.
 
Upvote 0
Hi Norie,

Here is what I have so far. It only allows the user to click the enter button when the combination of textbox/ combobox are not empty. At the same time I had to put in contingency options to make sure that it checks the other textbox/ combobox combinations before making the decision on whether or not to activate the Enter button.

I have pasting the code for the first two textbox/ combobox combinations. The rest are practically the same with only differences in object reference numbers.

Code: 
Private Sub UserForm_Initialize()
 
Dim MyArray                         As Variant
Dim Ctr                             As Integer
 
 
' Populate options on userform
    MyArray = Array("On duty", "Off duty ", "On call", "On vacation")
 
    For Ctr = LBound(MyArray) To UBound(MyArray)
        For n = 1 To 7
            Controls("ComboBox" & n).Enabled = False
            Controls("ComboBox" & n).AddItem MyArray(Ctr)
        Next n
    Next Ctr
End Sub
 
 
Private Sub TextBox1_Change()
 
' Make sure that the textbox and combobox has text in them before activating the Enter button, _
 but also safeguarding to keep the Enter button activated if another textbox/ combobox combination _
 are filled in
  ' Start off by disenabling the Enter button
    OK_Button.Enabled = False
 
  ' If the textbox is filled in then activate the combobox _
    Check the status of the other textbox/ comboboxes before activating/ deactivating the Enter button
    If TextBox1.Text <> "" Then
 
        ComboBox1.Enabled = True
 
        Select Case True
            Case TextBox2.Text <> "" And ComboBox2 = ""
                OK_Button.Enabled = False
            Case TextBox3.Text <> "" And ComboBox3 = ""
                OK_Button.Enabled = False
            Case TextBox4.Text <> "" And ComboBox4 = ""
                OK_Button.Enabled = False
            Case TextBox5.Text <> "" And ComboBox5 = ""
                OK_Button.Enabled = False
            Case TextBox6.Text <> "" And ComboBox6 = ""
                OK_Button.Enabled = False
            Case TextBox7.Text <> "" And ComboBox7 = ""
                OK_Button.Enabled = False
            Case Else
                OK_Button.Enabled = OK_Button.Enabled And ComboBox1.Enabled
        End Select
 
    Else
 
        ComboBox1.Enabled = False
 
        Select Case True
            Case TextBox2.Text <> "" And ComboBox2 <> ""
                OK_Button.Enabled = True
            Case TextBox3.Text <> "" And ComboBox3 <> ""
                OK_Button.Enabled = True
            Case TextBox4.Text <> "" And ComboBox4 <> ""
                OK_Button.Enabled = True
            Case TextBox5.Text <> "" And ComboBox5 <> ""
                OK_Button.Enabled = True
            Case TextBox6.Text <> "" And ComboBox6 <> ""
                OK_Button.Enabled = True
            Case TextBox7.Text <> "" And ComboBox7 <> ""
                OK_Button.Enabled = True
            Case Else
                OK_Button.Enabled = False
        End Select
 
    End If
End Sub
 
 
Private Sub ComboBox1_Change()
  ' Start off by disenabling the Enter button
    OK_Button.Enabled = False
 
  ' Enable the Enter button if the Textbox has something in it
    OK_Button.Enabled = (ComboBox1.Text <> "")
End Sub
 
 
Private Sub TextBox2_Change()
 
' Make sure that the textbox and combobox has text in them before activating the Enter button, _
 but also safeguarding to keep the Enter button activated if another textbox/ combobox combination _
 are filled in
  ' Start off by disenabling the Enter button
    OK_Button.Enabled = False
 
  ' If the textbox is filled in then activate the combobox _
    Check the status of the other textbox/ comboboxes before activating/ deactivating the Enter button
    If TextBox2.Text <> "" Then
 
        ComboBox2.Enabled = True
 
        Select Case True
            Case TextBox1.Text <> "" And ComboBox1 = ""
                OK_Button.Enabled = False
            Case TextBox3.Text <> "" And ComboBox3 = ""
                OK_Button.Enabled = False
            Case TextBox4.Text <> "" And ComboBox4 = ""
                OK_Button.Enabled = False
            Case TextBox5.Text <> "" And ComboBox5 = ""
                OK_Button.Enabled = False
            Case TextBox6.Text <> "" And ComboBox6 = ""
                OK_Button.Enabled = False
            Case TextBox7.Text <> "" And ComboBox7 = ""
                OK_Button.Enabled = False
            Case Else
                OK_Button.Enabled = OK_Button.Enabled And ComboBox2.Enabled
        End Select
 
    Else
 
        ComboBox2.Enabled = False
 
        Select Case True
            Case TextBox1.Text <> "" And ComboBox1 <> ""
                OK_Button.Enabled = True
            Case TextBox3.Text <> "" And ComboBox3 <> ""
                OK_Button.Enabled = True
            Case TextBox4.Text <> "" And ComboBox4 <> ""
                OK_Button.Enabled = True
            Case TextBox5.Text <> "" And ComboBox5 <> ""
                OK_Button.Enabled = True
            Case TextBox6.Text <> "" And ComboBox6 <> ""
                OK_Button.Enabled = True
            Case TextBox7.Text <> "" And ComboBox7 <> ""
                OK_Button.Enabled = True
            Case Else
                OK_Button.Enabled = False
        End Select
 
    End If
End Sub
 
 
Private Sub ComboBox2_Change()
  ' Start off by disenabling the Enter button
    OK_Button.Enabled = False
 
  ' Enable the Enter button if the Textbox has something in it
    OK_Button.Enabled = (ComboBox2.Text <> "")
End Sub

I know that its a lot of code, but I kept adding code to catch potential human input errors.

AMAS
 
Upvote 0
Hi Norie,

I can also keep the current structure if you could show me how to use a single change event sub for multiple form objects. This could at least in part eliminate the redundant code used for the multiple combobox change event subs.

Thanks.

AMAS
 
Upvote 0
I can't really do anything without some sample data to work with.

Doesn't need to be the actual data, just something that's representative of that you've got.
 
Upvote 0
Hi Norie,

Here is what my form looks like (slightly modified). Seven textboxes and seven comboboxes and one Enter button and one Cancel button.

The comboboxes have four options: ("On duty", "Off duty ", "On call", "On vacation")

Is this what you wanted or did I misunderstand you? If not, please let me know and I will post again.

Thanks.

AMAS

Form.jpg
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Userform inconsistent responses
Replies
1
Views
218
Candyman8019
C
K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
897
Kakaska
K
S
  • Solved
Userform
Replies
6
Views
426
Bosquedeguate
Bosquedeguate
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Question Question
expand code to search on userform more than column by selection from combobox
Replies
1
Views
653
abdelfattah
A

Forum statistics

Threads
1,224,586
Messages
6,179,716
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top