Forum Discussion
Solved
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 ...
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
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
Ok, 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
- For 99% of VBA code there is no need to activate or select anything, UNLESS you want to show that range to the user (and a couple of rare exceptions).So if you need to change the value of the range selected by the user, you can simply use code like:
header.Value = "Text into cell" 'Or: header.Formula = "=SUM(A1:A10)" 'Or header.Font.Bold = True