how to pass a variable no matter what it is

[miconian]

Hi,

I understand (I think) that you can pass a variable to another routine like this:

othersub Target:=Range("A5")

But I want to pass the value of Target to another routine, no matter what Target happens to be at the time.

Basically I want to say:

"Okay, another routine is taking over now, and that new routine needs to know the value of Target, whatever it is at this point."

How do you do that?

Thanks...

 

[miconian]

Another interesting thing is that this error seems to have to do with automation across Office applications, but my code does not refer to anything except excel...at least, that I know of...

 

[acw]

Hi

As in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 Then
validlist_Col1 Range(Target )
End If ' (If Target.Column = 1 Then)

If Target.Column = 2 Then
validlist_Col2 Range(Target )
End If ' (If Target.Column = 2 Then)

If Target.Column = 3 Then
validlist_Col3 Range(Target )
End If ' (If Target.Column = 3 Then)

End Sub

Tony

 

[miconian]

Runt-time error '1004'':

Method 'Range' of object '_Worksheet' failed

 

[acw]

Hi

Oops

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 Then
validlist_Col1 Range(Target.address )
End If ' (If Target.Column = 1 Then)

If Target.Column = 2 Then
validlist_Col2 Range(Target.address )
End If ' (If Target.Column = 2 Then)

If Target.Column = 3 Then
validlist_Col3 Range(Target.address )
End If ' (If Target.Column = 3 Then)

End Sub


Tony

 

[dk]

Miconian,

I can't get the error message to happen for me using your code. However, I think that your problem could be because of your use of the Selection keyword, although I'm not definitely sure. Change your code to get rid of the Selection keyword and see if you still experience the error e.g. your validlist_Col3 procedure should look something like this:-

Sub validlist_Col3(ByVal Target As Range)

    Select Case Target.Offset(0, -1).Value    'see what's in column B

    Case "blah"
        Target.Value = "blorg"

    Case "blech"
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="blah, blah, blah, blah, blah"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With

    End Select

End Sub

Also, there's nothing wrong at all with the way you are calling the procedures (re acw's advice). You should definitely leave your code as is, although it might look nicer if you put each if statement on one line e.g.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column = 1 Then validlist_Col1 Target
    If Target.Column = 2 Then validlist_Col2 Target
    If Target.Column = 3 Then validlist_Col3 Target

End Sub

HTH
Dan

 

[miconian]

dk, I made that change, and interestingly, the error is now

Run-time error '-2147417848 (80010108)':
Method 'Add' of object 'Validation' failed

And when I click 'debug,' it's the same stanza of code that was getting the error before.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="blah, blah"

Actually, instead of "blah, blah" there are 23 choices separated by commas. Some of them include underscores, apostrophes, and question marks, if that matters.

 

[miconian]

I just comented out all but the first three choices, and didn't get the error (while still using 'With Selection.validation').

The thing is, I need all those choices to be there...

 

[acw]

Hi

Did you try the

validlist_Col1 Range(Target.address )

approach. If so, did it work? If not, what error message did you get.


Tony

 

[miconian]

acw, that yields the same error as before:

Run-time error '-2147417848 (80010108)':

Automation error
The object invoked has disconnected from its clients.

 

[dk]

dk, I made that change, and interestingly, the error is now

....

....

Actually, instead of "blah, blah" there are 23 choices separated by commas. Some of them include underscores, apostrophes, and question marks, if that matters.

What's the actual code you're trying to use?

Dan