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...

 

[dk]

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

Tony,

I've got to ask - why do you think that this will fix this particular problem? What's your logic?

Regards,
Dan

 

[miconian]

Short-term workaround, so far so good, is to refer to a range instead of listing the validation choices in the codes.

Anyone know if this will make things slower/faster?

Also still interested in solving the problem itself. This workaround is not tested extensively yet anyway (by me).

 

[dk]

What is the code you're using? Difficult to know how to solve your problem without seeing it.

Dan

 

[acw]

minconian

I created a small spreadsheet with blech in B7

and used the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 3 Then
validlist_Col3 Range(Target.Address)
End If ' (If Target.Column = 3 Then)
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

When I moved from B7 to C7, the drop down list appeared in C7.

Again, with blah in B6, it put blorg in C6. No problem all worked ok.

dk

Logic is that while Target in the worksheet change sub is defined as a range, if you output the variable directly you don't get the range, but the value in the range. Target.address will provide the address of the cell. By converting the address into a range (as in range(target.address), it is in the form that the called sub requires.

Try stepping through the worksheet change sub and use the immeditate window to see the value in Target, Target.address etc.

Tony

 

[miconian]

dk,

I'm sorry, but I posted all of my non-redundant code earlier in the thread. I don't know what else to give you.

Tony,

I'm sorry, but I don't know what you're saying. The code does indeed work most of the time.

I think the issue must be that you can only enter a certain number of validation list choices in the code when you set up a validation list programatically.

 

[dk]

Miconian, can you post the exact code you're trying to use. Your earlier code had the line Formula1:="blah, blah, blah, blah, blah" but then you said Actually, instead of "blah, blah" there are 23 choices separated by commas. Some of them include underscores, apostrophes, and question marks, if that matters.. This is the code I'd like to see. I'm as keen to solve this now as you are

Tony, what I guess you're seeing is the value of the range variable when you hover your mouse over it or type debug.print target in the immediate window. Value is the default property of a range object, but there is not a conversion when passing variables from one procedure to another e.g. put this code into a worksheet module and change any cell value on that worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    ExpectsARange Target
End Sub

Private Sub ExpectsARange(Target As Range)
    MsgBox "Variable type of Target is " & TypeName(Target)
End Sub

Dan

 

[miconian]

Kieran on the vbaexpress board suggests that programatically validated list parameters are limited to 256 characters:

http://www.vbaexpress.com/forum/showthread.php?p=9197#post9197

 

[dk]

Glad you got an answer. In the future it would be polite of you to ALWAYS post the exact code you're using. What may seem to make no difference to you could be exactly the reason your code is failing. This helps prevent us wasting are time going down the wrong path.

Thanks,
Daniel

 

[miconian]

dk,

I didn't post the exact code because it contains confidential information.

Thank you for your continued help and efforts, which I do appreciate.