How do I do this?

[Blasting Cap]

I have a workbook with about 10 sheets in it, one for each of several
CSR's. On the first sheet, I plan to consolidate the data from the
other sheets, and on the second, third, fourth, fifth.... sheets, is a
sheet for each CSR to enter data.

On one column, I would like to have a dropdown box for each cell in that
column, that would have a CSR code in it. It's a small list, one that
would not require an external data source, but I'd want it to be just a
dropdown box on that cell.

How do I do that?


Also, on another cell - Customer Name - I'd like to populate that with
data from a SQL table based on a customer number. Is it possible to do
that in a dropdown box as well?

Any help appreciated.

BC

 

[Scott]

If you're trying to do the validation in VBA, here's some sample code
that does something similar to what you're looking for. (I included
some of the defaults too -- obtained from recording when I set the
validation)

Sub Test()
Dim WS As Worksheet
Dim Choices As String

Set WS = Worksheets("Sheet1")

Choices = "This" & "," & "Is" & "," & "A" & "," & "Test"

With WS.Range("A1:A20").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

To do the Customer Name one, someone else may have a better method
(I've not done much with database inquiries), but you can record
obtaining the list, then manipulate the macro to put the list of names
where you want (ie. in the validation list).

Scott

 

[Blasting Cap]

This kinda does what I want, except that I want this selection on each
sheet in the workbook, in column F2:F4000.

 

[Scott]

This will loop through all worksheets in your workbook.

Sub Test()
Dim WS As Worksheet
Dim Choices As String

Choices = "This" & "," & "Is" & "," & "A" & "," & "Test"

For Each WS In Worksheets
With WS.Range("F2:F4000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next
End Sub

Scott