Forum Discussion
can you 'set variable= workbook ' on click (from entry from an input box)?
I have this code where I get range data from a different workbook (than from where the code is run from) when prompted from an input box. I'd like to be able to capture this other workbook name as a variable and use it later in the code. However, I cannot get this to happen. Even if I try to set the workbook as a variable just after the line in the code for input box, it just gives me the name of the workbook where the code is run from.
So:
is there a way to do this?
is the workbook name actually stored as part of the range data?
is there a way to capture the workbook name when you highlight a range when prompted from a message box?
maybe set workbook name when a workbook other than the active workbook is clicked on?
Just brainstorming some ideas...I'm kind of lost for what to do. Currently I set the workbook to a static name, but I'd rather not do this...and allow the code to use the workbook regardless of what the name is.
Cheers and thanks,
Greg
GMSchaaf You need something along these lines:
Sub GetWbFromRange() Dim Rng As Variant On Error Resume Next 'User might cancel, then the next line will fail Set Rng = Application.InputBox("Select the range please", , , , , , , 8) On Error GoTo 0 If Not Rng Is Nothing Then MsgBox "Cell: " & Rng.Address & vbNewLine & "Worksheet: " & Rng.Parent.Name & vbNewLine & "Workbook: " & Rng.Parent.Parent.FullName End If End Sub
4 Replies
GMSchaaf You need something along these lines:
Sub GetWbFromRange() Dim Rng As Variant On Error Resume Next 'User might cancel, then the next line will fail Set Rng = Application.InputBox("Select the range please", , , , , , , 8) On Error GoTo 0 If Not Rng Is Nothing Then MsgBox "Cell: " & Rng.Address & vbNewLine & "Worksheet: " & Rng.Parent.Name & vbNewLine & "Workbook: " & Rng.Parent.Parent.FullName End If End SubOk, wow, that was bad. I didn't have a line such as
Dim Rng As Variant
When I add in the msgbox line, it is indeed storing the workbook name.
Q: now, how do I activate the workbook from the stored range?
Greg
