I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

I thought a combobox would be better. I am new to VBA. What would the code
be to completely create the combobox with only 1-8 as a choice?

Also, what is the difference between a listbox and combobox?

Thanks,
Les



"equiangular" wrote:

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

Add code to UserForm_Initialize() event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
ComboBox2.AddItem "2"
'same for adding 3 to 8
End Sub

Combo box can only display 1 row while listbox can display several rows
Also combo box allows user to type in other values if style is set to 0
- frmStyleDropDownCombo

WLMPilot wrote:
I thought a combobox would be better. I am new to VBA. What would the code
be to completely create the combobox with only 1-8 as a choice?

Also, what is the difference between a listbox and combobox?

Thanks,
Les



"equiangular" wrote:

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

Thanks.

After thinking about the data I had on the worksheet ("Items"), I have a
better way to populate the combobox, but don't know the code to do it. This
spreadsheet is for each ambulance station (base) in the county to reorder
supplies. On the Items sheet, I have each station number (actually 1-7, not
1-8) as a column header starting with C3. If a new station is built, then a
new set of numbers will go in the next column with the station number at the
top. Therefore, if I can just get the code that will pick up the value in
these cells, then I would not have to edit the formula if a new station is
built. How can I do this?

Thanks again,
Les

"equiangular" wrote:

Add code to UserForm_Initialize() event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
ComboBox2.AddItem "2"
'same for adding 3 to 8
End Sub

Combo box can only display 1 row while listbox can display several rows
Also combo box allows user to type in other values if style is set to 0
- frmStyleDropDownCombo

WLMPilot wrote:
I thought a combobox would be better. I am new to VBA. What would the code
be to completely create the combobox with only 1-8 as a choice?

Also, what is the difference between a listbox and combobox?

Thanks,
Les



"equiangular" wrote:

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

You may try the rowsource property, for example

Dim r As Range

Set r = Worksheets("Sheet1").Range("C3")

Set r = Range(r, r.End(xlToRight))

ComboBox1.RowSource = r.Address

Set r = Nothing

WLMPilot wrote:
Thanks.

After thinking about the data I had on the worksheet ("Items"), I have a
better way to populate the combobox, but don't know the code to do it. This
spreadsheet is for each ambulance station (base) in the county to reorder
supplies. On the Items sheet, I have each station number (actually 1-7, not
1-8) as a column header starting with C3. If a new station is built, then a
new set of numbers will go in the next column with the station number at the
top. Therefore, if I can just get the code that will pick up the value in
these cells, then I would not have to edit the formula if a new station is
built. How can I do this?

Thanks again,
Les

"equiangular" wrote:

Add code to UserForm_Initialize() event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
ComboBox2.AddItem "2"
'same for adding 3 to 8
End Sub

Combo box can only display 1 row while listbox can display several rows
Also combo box allows user to type in other values if style is set to 0
- frmStyleDropDownCombo

WLMPilot wrote:
I thought a combobox would be better. I am new to VBA. What would the code
be to completely create the combobox with only 1-8 as a choice?

Also, what is the difference between a listbox and combobox?

Thanks,
Les



"equiangular" wrote:

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

Thanks, I will give it a try today. One question in trying to understand
the code: Are you setting the value in the combobox to the address or the
value in the specific cell with "Combobox1.RowSource = r.Address"?

Les

"equiangular" wrote:

You may try the rowsource property, for example

Dim r As Range

Set r = Worksheets("Sheet1").Range("C3")

Set r = Range(r, r.End(xlToRight))

ComboBox1.RowSource = r.Address

Set r = Nothing

WLMPilot wrote:
Thanks.

After thinking about the data I had on the worksheet ("Items"), I have a
better way to populate the combobox, but don't know the code to do it. This
spreadsheet is for each ambulance station (base) in the county to reorder
supplies. On the Items sheet, I have each station number (actually 1-7, not
1-8) as a column header starting with C3. If a new station is built, then a
new set of numbers will go in the next column with the station number at the
top. Therefore, if I can just get the code that will pick up the value in
these cells, then I would not have to edit the formula if a new station is
built. How can I do this?

Thanks again,
Les

"equiangular" wrote:

Add code to UserForm_Initialize() event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
ComboBox2.AddItem "2"
'same for adding 3 to 8
End Sub

Combo box can only display 1 row while listbox can display several rows
Also combo box allows user to type in other values if style is set to 0
- frmStyleDropDownCombo

WLMPilot wrote:
I thought a combobox would be better. I am new to VBA. What would the code
be to completely create the combobox with only 1-8 as a choice?

Also, what is the difference between a listbox and combobox?

Thanks,
Les



"equiangular" wrote:

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les

Right
You can assign a range to RowSource property to populate the combobox

WLMPilot wrote:
Thanks, I will give it a try today. One question in trying to understand
the code: Are you setting the value in the combobox to the address or the
value in the specific cell with "Combobox1.RowSource = r.Address"?

Les

"equiangular" wrote:

You may try the rowsource property, for example

Dim r As Range

Set r = Worksheets("Sheet1").Range("C3")

Set r = Range(r, r.End(xlToRight))

ComboBox1.RowSource = r.Address

Set r = Nothing

WLMPilot wrote:
Thanks.

After thinking about the data I had on the worksheet ("Items"), I have a
better way to populate the combobox, but don't know the code to do it. This
spreadsheet is for each ambulance station (base) in the county to reorder
supplies. On the Items sheet, I have each station number (actually 1-7, not
1-8) as a column header starting with C3. If a new station is built, then a
new set of numbers will go in the next column with the station number at the
top. Therefore, if I can just get the code that will pick up the value in
these cells, then I would not have to edit the formula if a new station is
built. How can I do this?

Thanks again,
Les

"equiangular" wrote:

Add code to UserForm_Initialize() event

Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
ComboBox2.AddItem "2"
'same for adding 3 to 8
End Sub

Combo box can only display 1 row while listbox can display several rows
Also combo box allows user to type in other values if style is set to 0
- frmStyleDropDownCombo

WLMPilot wrote:
I thought a combobox would be better. I am new to VBA. What would the code
be to completely create the combobox with only 1-8 as a choice?

Also, what is the difference between a listbox and combobox?

Thanks,
Les



"equiangular" wrote:

1. In order to stay the focus, you hv to use Cancel=True instead of
txtST.SetFocus
2. If you only allow the user to enter 1-8, I think it's better to use a
combobox instead and add the code to UserForm_Initialize() like this
Private Sub UserForm_Initialize()
ComboBox1.AddItem "1"
'.....
End Sub

WLMPilot wrote:
I wanted to create a routine to make sure the user not only entered something
in the textbox of the userform, but that the answer is a valid answer. The
routine below is for checking to be sure something was entered. However, it
does not seem to work. Any suggestions?

Private Sub txtST_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtST = "" Then
txtST.SetFocus
End If
End Sub

Now for the second part. The field, txtST, will contain a value from 1 to
8. I would actually like to have a listbox for this field so the user can
choose and I do not have to verify if input is correct. How do I do this?

Thanks,
Les