Help with the following code??
 

I have a userform with a series of textboxes and one combobox set to dropdown
a list of the 50 states. That works just fine, however I would like it to
when the user selects say Alabama from the dropdown and clicks the 'ADD"
button the value entered in to the database to be its abbriviation 'AL' not
the full name. Now I have used this code within my database column but I
would like it to apply to the combobox and I am not sure how to do it?

If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If

Help with the following code??
 

I'm not clear. How did you use it in your database column?

Also, what is the code of the combo box _change event?


On Oct 9, 10:29 am, Mekinnik
wrote:
I have a userform with a series of textboxes and one combobox set to dropdown
a list of the 50 states. That works just fine, however I would like it to
when the user selects say Alabama from the dropdown and clicks the 'ADD"
button the value entered in to the database to be its abbriviation 'AL' not
the full name. Now I have used this code within my database column but I
would like it to apply to the combobox and I am not sure how to do it?

If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If

Help with the following code??
 

I atteched it directly to my worksheet and it worked in conjuction with a
data validation within the column.

That is my question I basiclly want a change event code that will copy the
state abbreviation to the database when the user selects the full state name.

"dan dungan" wrote:

I'm not clear. How did you use it in your database column?

Also, what is the code of the combo box _change event?


On Oct 9, 10:29 am, Mekinnik
wrote:
I have a userform with a series of textboxes and one combobox set to dropdown
a list of the 50 states. That works just fine, however I would like it to
when the user selects say Alabama from the dropdown and clicks the 'ADD"
button the value entered in to the database to be its abbriviation 'AL' not
the full name. Now I have used this code within my database column but I
would like it to apply to the combobox and I am not sure how to do it?

If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If

Help with the following code??
 

Did you attach a combo box to your worksheet?
Which excel version are you using?

On Oct 9, 11:14 am, Mekinnik
wrote:
I atteched it directly to my worksheet and it worked in conjuction with a
data validation within the column.

That is my question I basiclly want a change event code that will copy the
state abbreviation to the database when the user selects the full state name.

"dan dungan" wrote:
I'm not clear. How did you use it in your database column?

Also, what is the code of the combo box _change event?

On Oct 9, 10:29 am, Mekinnik
wrote:
I have a userform with a series of textboxes and one combobox set to dropdown
a list of the 50 states. That works just fine, however I would like it to
when the user selects say Alabama from the dropdown and clicks the 'ADD"
button the value entered in to the database to be its abbriviation 'AL' not
the full name. Now I have used this code within my database column but I
would like it to apply to the combobox and I am not sure how to do it?

If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If

Help with the following code??
 

I basiclly want a change event code that will copy the state abbreviation to
the database when the user selects the full state name from the combobox on
the user form.

"dan dungan" wrote:

Did you attach a combo box to your worksheet?
Which excel version are you using?

On Oct 9, 11:14 am, Mekinnik
wrote:
I atteched it directly to my worksheet and it worked in conjuction with a
data validation within the column.

That is my question I basiclly want a change event code that will copy the
state abbreviation to the database when the user selects the full state name.

"dan dungan" wrote:
I'm not clear. How did you use it in your database column?

Also, what is the code of the combo box _change event?

On Oct 9, 10:29 am, Mekinnik
wrote:
I have a userform with a series of textboxes and one combobox set to dropdown
a list of the 50 states. That works just fine, however I would like it to
when the user selects say Alabama from the dropdown and clicks the 'ADD"
button the value entered in to the database to be its abbriviation 'AL' not
the full name. Now I have used this code within my database column but I
would like it to apply to the combobox and I am not sure how to do it?

If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If