First of all, I wanna let all you guys know you are great and have taught me
a lot of excel in little over a week here.

So I have a little brain teaser.

I am making a spreadsheet with three drop down lists, the last two dependent
on succession. From the first, you pick either the word "foreign" or
"domestic." That is followed in the next column by a list that will let you
pick either a US State or a national country, depending on your first
selection. The last column is a city column that will let you pick a city in
one of the state/countries. To make scrolling easier, I looked up your advice
on combo boxes and played around in VBA until I finally got it working...
almost.

For all of the certain states or countries which have two words in the name
(New Hampshire, Saudi Arabia, etc.) the combo box for the following (city)
drop down comes up blank when i double-click, but the original data
validation list still drops down the names. Essentially, I can't use the
combo box to pick a city from any state/country with two names, but I know
the formula is still there because I can select it from the validation
drop-down.

Here is the part of VBA which I think pertains to this- and I am not sure
because I have never done anything in VBA:

If Target.Validation.Type = 3 Then
Application.EnableEvents = False
If Target.Column = 3 Then
str = Target.Validation.Formula1
Else
str = "=" & Target.Offset(0, -1).Value
End If
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate


--Column three is the very first one (foreign/domestic).
I got the VBA straight off of the excel example for dependent combo box
validation lists on contextures.com (GREAT website by the way, thank you for
making it easy for those of us less capable) and made a couple of necessary
adjustments to make it fit mine.

Sorry for making you read all this, and thanks in advance if you did.

Have a look at http://www.contextures.com/xlDataVal02.html#TwoWord

"BB" wrote:

First of all, I wanna let all you guys know you are great and have taught me
a lot of excel in little over a week here.

So I have a little brain teaser.

I am making a spreadsheet with three drop down lists, the last two dependent
on succession. From the first, you pick either the word "foreign" or
"domestic." That is followed in the next column by a list that will let you
pick either a US State or a national country, depending on your first
selection. The last column is a city column that will let you pick a city in
one of the state/countries. To make scrolling easier, I looked up your advice
on combo boxes and played around in VBA until I finally got it working...
almost.

For all of the certain states or countries which have two words in the name
(New Hampshire, Saudi Arabia, etc.) the combo box for the following (city)
drop down comes up blank when i double-click, but the original data
validation list still drops down the names. Essentially, I can't use the
combo box to pick a city from any state/country with two names, but I know
the formula is still there because I can select it from the validation
drop-down.

Here is the part of VBA which I think pertains to this- and I am not sure
because I have never done anything in VBA:

If Target.Validation.Type = 3 Then
Application.EnableEvents = False
If Target.Column = 3 Then
str = Target.Validation.Formula1
Else
str = "=" & Target.Offset(0, -1).Value
End If
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate


--Column three is the very first one (foreign/domestic).
I got the VBA straight off of the excel example for dependent combo box
validation lists on contextures.com (GREAT website by the way, thank you for
making it easy for those of us less capable) and made a couple of necessary
adjustments to make it fit mine.

Sorry for making you read all this, and thanks in advance if you did.

Thanks for the speedy reply.

I originally did get my formula from that website. My current formula in the
cell validation is =INDIRECT(SUBSTITUTE(D5," ","")).

The validation list picks this up fine, and the cities show up. But the
combo box does not pick up the formula. Do I need something more complicated?

"PegL" wrote:

Have a look at http://www.contextures.com/xlDataVal02.html#TwoWord

"BB" wrote:

First of all, I wanna let all you guys know you are great and have taught me
a lot of excel in little over a week here.

So I have a little brain teaser.

I am making a spreadsheet with three drop down lists, the last two dependent
on succession. From the first, you pick either the word "foreign" or
"domestic." That is followed in the next column by a list that will let you
pick either a US State or a national country, depending on your first
selection. The last column is a city column that will let you pick a city in
one of the state/countries. To make scrolling easier, I looked up your advice
on combo boxes and played around in VBA until I finally got it working...
almost.

For all of the certain states or countries which have two words in the name
(New Hampshire, Saudi Arabia, etc.) the combo box for the following (city)
drop down comes up blank when i double-click, but the original data
validation list still drops down the names. Essentially, I can't use the
combo box to pick a city from any state/country with two names, but I know
the formula is still there because I can select it from the validation
drop-down.

Here is the part of VBA which I think pertains to this- and I am not sure
because I have never done anything in VBA:

If Target.Validation.Type = 3 Then
Application.EnableEvents = False
If Target.Column = 3 Then
str = Target.Validation.Formula1
Else
str = "=" & Target.Offset(0, -1).Value
End If
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate


--Column three is the very first one (foreign/domestic).
I got the VBA straight off of the excel example for dependent combo box
validation lists on contextures.com (GREAT website by the way, thank you for
making it easy for those of us less capable) and made a couple of necessary
adjustments to make it fit mine.

Sorry for making you read all this, and thanks in advance if you did.