Prev: Copy Active Range from one workbook to another
Next: OLE Problem on Opening
From: Brian on 16 Dec 2009 18:49
I am having trouble getting a text box to auto fill from a choice made from a
combo box.

Combo Box Name is Address_21
Text Box Name is City_1

My code is as follows:

'Auto Fill CES Spec Written Office

Private Sub ShowCity_1()
Dim Address_21 As Variant
Dim City_1 As Variant

If UserForm1.Address_21 = "2306 Long Green Court" Then UserForm1.City_1
= Valrico
If UserForm1.Address_21 = "3440 Oakcliff Road" Then UserForm1.City_1 =
Atlanta
If UserForm1.Address_21 = "7015 East 14th Aveneue" Then UserForm1.City_1
= Tampa
If UserForm1.Address_21 = "3890 Oakcliff Industrial Court" Then
UserForm1.City_1 = Atlanta
If UserForm1.Address_21 = "1800 Taylor Drive" Then UserForm1.City_1 =
Gastonia

End Sub

What did i do wrong or miss?
From: B Lynn B on 16 Dec 2009 19:03
Enclose each city name in double quotation marks. Otherwise VBA thinks
you're refering to a variable.

"Brian" wrote:

> I am having trouble getting a text box to auto fill from a choice made from a
> combo box.
>
> Combo Box Name is Address_21
> Text Box Name is City_1
>
> My code is as follows:
>
> 'Auto Fill CES Spec Written Office
>
> Private Sub ShowCity_1()
> Dim Address_21 As Variant
> Dim City_1 As Variant
>
> If UserForm1.Address_21 = "2306 Long Green Court" Then UserForm1.City_1
> = Valrico
> If UserForm1.Address_21 = "3440 Oakcliff Road" Then UserForm1.City_1 =
> Atlanta
> If UserForm1.Address_21 = "7015 East 14th Aveneue" Then UserForm1.City_1
> = Tampa
> If UserForm1.Address_21 = "3890 Oakcliff Industrial Court" Then
> UserForm1.City_1 = Atlanta
> If UserForm1.Address_21 = "1800 Taylor Drive" Then UserForm1.City_1 =
> Gastonia
>
> End Sub
>
> What did i do wrong or miss?
From: Brian on 16 Dec 2009 19:21
I changed the city name to the following. When I go to the combo box and
choose my Address nothing happens. I missed something.

"Valrico"
"Atlanta"
"Tampa"
"Atlanta"
"Gastonia"


"B Lynn B" wrote:

> Enclose each city name in double quotation marks. Otherwise VBA thinks
> you're refering to a variable.
>
> "Brian" wrote:
>
> > I am having trouble getting a text box to auto fill from a choice made from a
> > combo box.
> >
> > Combo Box Name is Address_21
> > Text Box Name is City_1
> >
> > My code is as follows:
> >
> > 'Auto Fill CES Spec Written Office
> >
> > Private Sub ShowCity_1()
> > Dim Address_21 As Variant
> > Dim City_1 As Variant
> >
> > If UserForm1.Address_21 = "2306 Long Green Court" Then UserForm1.City_1
> > = Valrico
> > If UserForm1.Address_21 = "3440 Oakcliff Road" Then UserForm1.City_1 =
> > Atlanta
> > If UserForm1.Address_21 = "7015 East 14th Aveneue" Then UserForm1.City_1
> > = Tampa
> > If UserForm1.Address_21 = "3890 Oakcliff Industrial Court" Then
> > UserForm1.City_1 = Atlanta
> > If UserForm1.Address_21 = "1800 Taylor Drive" Then UserForm1.City_1 =
> > Gastonia
> >
> > End Sub
> >
> > What did i do wrong or miss?
From: B Lynn B on 16 Dec 2009 19:53
Sorry, I should have looked more closely at everything that was going on. I
just quickly scanned and identified the most obvious thing that jumped out at
me.

Paste the following into your userform code.

Private Sub Address_21_Change()

Select Case Address_21
Case ""2306 Long Green Court"
City_1 = "Valrico"
Case ""3440 Oakcliff Road"
City_1 = "Atlanta"
Case "7015 East 14th Aveneue"
City_1 = "Tampa"
Case "1800 Taylor Drive"
City_1 = "Gastonia"
End Select

End Sub



"Brian" wrote:

> I changed the city name to the following. When I go to the combo box and
> choose my Address nothing happens. I missed something.
>
> "Valrico"
> "Atlanta"
> "Tampa"
> "Atlanta"
> "Gastonia"
>
>
> "B Lynn B" wrote:
>
> > Enclose each city name in double quotation marks. Otherwise VBA thinks
> > you're refering to a variable.
> >
> > "Brian" wrote:
> >
> > > I am having trouble getting a text box to auto fill from a choice made from a
> > > combo box.
> > >
> > > Combo Box Name is Address_21
> > > Text Box Name is City_1
> > >
> > > My code is as follows:
> > >
> > > 'Auto Fill CES Spec Written Office
> > >
> > > Private Sub ShowCity_1()
> > > Dim Address_21 As Variant
> > > Dim City_1 As Variant
> > >
> > > If UserForm1.Address_21 = "2306 Long Green Court" Then UserForm1.City_1
> > > = Valrico
> > > If UserForm1.Address_21 = "3440 Oakcliff Road" Then UserForm1.City_1 =
> > > Atlanta
> > > If UserForm1.Address_21 = "7015 East 14th Aveneue" Then UserForm1.City_1
> > > = Tampa
> > > If UserForm1.Address_21 = "3890 Oakcliff Industrial Court" Then
> > > UserForm1.City_1 = Atlanta
> > > If UserForm1.Address_21 = "1800 Taylor Drive" Then UserForm1.City_1 =
> > > Gastonia
> > >
> > > End Sub
> > >
> > > What did i do wrong or miss?
From: B Lynn B on 16 Dec 2009 20:04
Oops, I see that I missed one of the combos. Or maybe got them mixed up.
But you get the drift and can correct as needed.

Another thought would be to employ the combo box Column property to load and
retrieve the associated city value that way. Go read the help entry on the
subject for further inspiration in that direction.

"B Lynn B" wrote:

> Sorry, I should have looked more closely at everything that was going on. I
> just quickly scanned and identified the most obvious thing that jumped out at
> me.
>
> Paste the following into your userform code.
>
> Private Sub Address_21_Change()
>
> Select Case Address_21
> Case ""2306 Long Green Court"
> City_1 = "Valrico"
> Case ""3440 Oakcliff Road"
> City_1 = "Atlanta"
> Case "7015 East 14th Aveneue"
> City_1 = "Tampa"
> Case "1800 Taylor Drive"
> City_1 = "Gastonia"
> End Select
>
> End Sub
>
>
>
> "Brian" wrote:
>
> > I changed the city name to the following. When I go to the combo box and
> > choose my Address nothing happens. I missed something.
> >
> > "Valrico"
> > "Atlanta"
> > "Tampa"
> > "Atlanta"
> > "Gastonia"
> >
> >
> > "B Lynn B" wrote:
> >
> > > Enclose each city name in double quotation marks. Otherwise VBA thinks
> > > you're refering to a variable.
> > >
> > > "Brian" wrote:
> > >
> > > > I am having trouble getting a text box to auto fill from a choice made from a
> > > > combo box.
> > > >
> > > > Combo Box Name is Address_21
> > > > Text Box Name is City_1
> > > >
> > > > My code is as follows:
> > > >
> > > > 'Auto Fill CES Spec Written Office
> > > >
> > > > Private Sub ShowCity_1()
> > > > Dim Address_21 As Variant
> > > > Dim City_1 As Variant
> > > >
> > > > If UserForm1.Address_21 = "2306 Long Green Court" Then UserForm1.City_1
> > > > = Valrico
> > > > If UserForm1.Address_21 = "3440 Oakcliff Road" Then UserForm1.City_1 =
> > > > Atlanta
> > > > If UserForm1.Address_21 = "7015 East 14th Aveneue" Then UserForm1.City_1
> > > > = Tampa
> > > > If UserForm1.Address_21 = "3890 Oakcliff Industrial Court" Then
> > > > UserForm1.City_1 = Atlanta
> > > > If UserForm1.Address_21 = "1800 Taylor Drive" Then UserForm1.City_1 =
> > > > Gastonia
> > > >
> > > > End Sub
> > > >
> > > > What did i do wrong or miss?
 |  Next  |  Last
Pages: 1 2
Prev: Copy Active Range from one workbook to another
Next: OLE Problem on Opening