Selecting a specific cell (that is data validated)

[cliodne]

This is probably insanely easy, but it's driving me mad. I'm trying t
select a cell that's been data validated to a list (so it's a cell tha
has a scroll down to select more cell value options). I'm using th
standard

Sheets("Expenses").Select
Range("D8").Select
A = ActiveCell.Value
Range("D9").Select
C = ActiveCell.Value

Is there any way to select a cell that's on data validation list?

I can always re-route the string in that cell to a different cel
that's not data validated, but if there's a streamlined solution, tha
would be wonderful.

Thanks,
Cam

 

[Guest]

It's working fine for me. What is happening or not happening that you are
expecting to happen?

You can also get the value from the cell without actually selecting it.

Sheets("Expenses").Select
A = Range("D8")
C = Range("D9")
(or)
A = Range("D8").Value
C = Range("D9").Value

 

[Guest]

It is not clear what you are asking, but run this macro on your sheet and see
if it gives you any idea about achieving what you want:

Sub abc()
Dim rng1 As Range, rng2 As Range
Dim rng As Range, cell As Range
Dim s As String
Set rng = Nothing
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
Set rng1 = Nothing
If Not rng Is Nothing Then
For Each cell In rng
If cell.Validation.Type = xlValidateList Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next cell
If Not rng1 Is Nothing Then
For Each cell In rng1
s = cell.Validation.Formula1
Set rng2 = Nothing
On Error Resume Next
Set rng2 = Range(s)
On Error GoTo 0
cell.Select
If Not rng2 Is Nothing Then
MsgBox "source list is " & rng2.Address(external:=True)
Else
MsgBox "Source list is " & s
End If
Next
End If
End If
End Sub

 

[cliodne]

Okay, sorry, I'll try to explain my problem better. The following is my
code currently.


Code:
--------------------
Private Sub Worksheet_Activate()
'
'
Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim D As Variant
'
Range("D8").Select
A = ActiveCell.Value
Range("C33").Select
C = ActiveCell.Value
'
Sheets("Revenue").Select
Range("D8").Select
B = ActiveCell.Value
Range("C33").Select
D = ActiveCell.Value
'
'
If A <> B Or C <> D Then
MsgBox "Please make sure that the currency choice and percentage of attendance are uniform for all sheets before viewing this page. Thank you."
End If
'
'
End Sub

--------------------


The ranges selected are cells that when you click on them, they drop
down a list of choices (data validation set to the list option), so I'm
trying to get the code to read what exact value it's on at the moment,
but when I step through the code, I get an error saying: > Run-time error '1004': Application-defined or object-defined error. So I changed my variables to variant to cover everything, but still
the problem. When I do the direct A = Range("D8"), A doesn't pick up
anything and is set at "nothing"

I hope this is clearer,
Cami

 

[Tom Ogilvy]

Private Sub Worksheet_Activate()
'
If Range("D8").Value <> Worksheets("Revenue").Range("D8").Value or _
Range("C33").Value <> Worksheets("Revenue").Range("C33").Value _
Then
MsgBox "Please make sure that the currency choice and percentage of
attendance are uniform for all sheets before viewing this page. Thank you."
End If
'
'
End Sub


--
Regards,
Tom Ogilvy

Okay, sorry, I'll try to explain my problem better. The following is my
code currently.


Code:
--------------------
Private Sub Worksheet_Activate()
'
'
Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim D As Variant
'
Range("D8").Select
A = ActiveCell.Value
Range("C33").Select
C = ActiveCell.Value
'
Sheets("Revenue").Select
Range("D8").Select
B = ActiveCell.Value
Range("C33").Select
D = ActiveCell.Value
'
'
If A <> B Or C <> D Then
MsgBox "Please make sure that the currency choice and percentage of

attendance are uniform for all sheets before viewing this page. Thank you."

End If
'
'
End Sub

--------------------


The ranges selected are cells that when you click on them, they drop
down a list of choices (data validation set to the list option), so I'm
trying to get the code to read what exact value it's on at the moment,
but when I step through the code, I get an error saying: > Run-time error

'1004': Application-defined or object-defined error. So I changed my
variables to variant to cover everything, but still

 

[cliodne]

Thank you Tom. I must remember that format - works like a charm,

Cami