Data Validation is it proper to use it in this situation

[billabong]

Hello

Im a beginner to VBA in excel and this is probably the wrong way to go
about it...

But I want to have a list of companies in a column. When the user
selects a company that will create a column with the "attention name"
with respect to the company. Then have both company and attetnion
appear in another column. I have done this with excels data
validation for the companies name but am having problems with the
attention part. Ive been trying with the apollo part only using
select case

Any suggestions is appreciated
Lisa

Select Case Cells(5, "c")
Case "Apollo"
Cells(2, "A") = "APOLLO "

'Cells(9, "h") = "Attention smith"
'Cells(10, "h") = "Attention john"

With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$H$9:$H$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Cells(3, "a") = Cells(5, "e")

Case "brooks"
Cells(2, "a") = "Brooks ltd"

Case "peterson"
Cells(2, "a") = "peterson ltd"


Case Else
Cells(2, "B") = ""


End Select

 

[Tim Childs]

Hi

I am no expert but you may need to clarify your request a bit to get a
response - no-one has replied so far.

As I understand it, you want to ensure that as the data is entered the
attention column is within certain parameters e.g. containing either "john"
or "Smith"

Data validation appears a long hand way as Excel as the facility to do
checking each time:

Private Sub Worksheet_Change(ByVal Target As Range)
'do your checking on the change here
End Sub

It is in the "sheet" object in VBA

I recommend you look in Chip Pearson' s site (www.Cpearson.com) or search
the Google archives, perhaps using the word "worksheet_change" etc

I hope that gets you started

Tim

 

[Dave Peterson]

I don't think I'd use VBA for this.

You've got a list of company names (in a different (hidden) worksheet) that you
use for Data|validation.

Next to those company names, put the attention name.

then you can use a vlookup() formula to get that attention name.

=if(c5="","",vlookup(c5,sheet3!$a$1:$b$9999,2,false))

I find it easier to update a table in a worksheet when things change than in the
code.

 

[Tim Childs]

Dave

very neat - Iwasn't entirely sure what was going on (and that was when my
mind was still fresh in the morning <g>)

Tim