Selecting a specific cell (that is data validated)

This is probably insanely easy, but it's driving me mad. I'm trying to select a cell that's been data validated to a list (so it's a cell that has a scroll down to select more cell value options). I'm using the 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 cell that's not data validated, but if there's a streamlined solution, that would be wonderful.

Thanks,
Cami

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


"cliodne" wrote:

>
> This is probably insanely easy, but it's driving me mad. I'm trying to
> select a cell that's been data validated to a list (so it's a cell that
> has a scroll down to select more cell value options). I'm using the
> 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 cell
> that's not data validated, but if there's a streamlined solution, that
> would be wonderful.
>
> Thanks,
> Cami
>
>
> --
> cliodne
> ------------------------------------------------------------------------
> cliodne's Profile: http://www.excelforum.com/member.php...o&userid=28774
> View this thread: http://www.excelforum.com/showthread...hreadid=534873
>
>

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

--
Regards,
Tom Ogilvy


"cliodne" wrote:

>
> This is probably insanely easy, but it's driving me mad. I'm trying to
> select a cell that's been data validated to a list (so it's a cell that
> has a scroll down to select more cell value options). I'm using the
> 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 cell
> that's not data validated, but if there's a streamlined solution, that
> would be wonderful.
>
> Thanks,
> Cami
>
>
> --
> cliodne
> ------------------------------------------------------------------------
> cliodne's Profile: http://www.excelforum.com/member.php...o&userid=28774
> View this thread: http://www.excelforum.com/showthread...hreadid=534873
>
>

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

Please Login or Register  to view this content.

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

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


"cliodne" <cliodne.26mxam_1145657402.042@excelforum-nospam.com> wrote in
message news:cliodne.26mxam_1145657402.042@excelforum-nospam.com...
>
> 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
>
>
> --
> cliodne
> ------------------------------------------------------------------------
> cliodne's Profile:
http://www.excelforum.com/member.php...o&userid=28774
> View this thread: http://www.excelforum.com/showthread...hreadid=534873
>

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

Cami