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]

Huh. Problem went away after I quit and restarted Excel. Don't know if it had to do with the fact that I also had changed the secondary procedures to ByVal. Also, I had to exit Excel with task manager; when I tried to do it the normal way, I actually got a msgbox that said "cannot exit Microsoft Excel." Weird.

 

[miconian]

Dang, it happened again! Here's the exact error:

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

Automation error
The object invoked has disconnected from its clients.

 

[miconian]

Then I chose "debug," and the code that turned yellow was this:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="apple, orange, pear, banana, mango, peach"

The fruit is just a fill-in here; there were actually many more items. Some of them included apostrophes and question marks. Could that matter?

Addendum: I took out the special characters and I still get the error.

Maybe I have too many "cases" in my "select case"?

 

[dk]

What's your full code?

 

[Legacy 21301]

http://support.microsoft.com/default.aspx?scid=kb;en-us;319832&Product=xlw

 

[miconian]

My code is like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

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

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

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x

If Target.Column < 15 Then

    ActiveCell.Offset(0, 1).Select 'move selection one cell to the right

' Show validation list (if there is one) in selected cell
On Error Resume Next
x = ActiveCell.validation.Type
If Err = 0 Then SendKeys "%{down}"
Err.Clear
End If

End Sub

Sub validlist_Col3(ByVal Target As Range)

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

    Case "blah"
        ActiveCell.Value = "blorg"

    Case "blech"
        With Selection.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

Then there are about seven more Cases, all very similar.

End Select

End Sub

I also have Sub validlist_Col2 and Col1, very similar.

 

[miconian]

Ponsy: I'm reading the stuff you linked to, but most of it is above me at this point. Any further hints would be very much appreciated.

 

[acw]

Hi

Try changing the calling routine to the format

validlist_Col1 Range(Target )

Tony

 

[miconian]

You mean to start the sub with:

Sub validlist_Col1 Range(Target)

?

VBA won't accept it. The text turns red, and it doesn't even give a horizontal line to show it recognizes the beginning of a new prodedure. Am I misunderstanding?

 

[miconian]

For what it's worth, the line that lights up yellow after the mysterious error is:

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

...which is part of a Select Case procedure, i.e.

Case "blah"
        With Selection.validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="blah, blah, blah"