You don't have to scroll. Have the user select the first cell (the one that represents the upper left corner of the range), then enter the last cell in the name box (right bottom corner), press the Shift key, and press the Enter key. Then, you can have the caption on your button:
"Select range and click here" and assign a string variable to Selection.Address.
If you really have your heart set on using an textbox on the menu bar, then in a test workbook, follow these steps:
Step 1
Close all workbooks in Excel except the test workbook you are working with.
Step 2
Find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module.
Code:
Private Sub Workbook_Open()
Run "ResetMenu"
Run "MakeTextBox"
Dim TymeOfDay$
If Time < 0.5 Then
TymeOfDay = "Good Morning !!" & vbCrLf & vbCrLf
ElseIf Time >= 0.5 And Time < 0.75 Then
TymeOfDay = "Good Afternoon !!" & vbCrLf & vbCrLf
Else
TymeOfDay = "Good Evening !!" & vbCrLf & vbCrLf
End If
MsgBox _
TymeOfDay & _
"A text box has been placed on the menu bar." & vbCrLf & vbCrLf & _
"Use that text box to enter a cell address or range," & vbCrLf & _
"and confirm the entry by pressing the Enter key.", 64, "Tip:"
End Sub
Private Sub Workbook_Activate()
Run "ResetMenu"
Run "MakeTextBox"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "ResetMenu"
ThisWorkbook.Save
End Sub
Private Sub Workbook_Deactivate()
Run "ResetMenu"
End Sub
Step 3
While you are in the Visual Basic Editor, click Insert > Menu and paste this in:
Code:
Private Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Custom Text Box").Delete
Err.Clear
End Sub
Private Sub MakeTextBox()
With Application
.ScreenUpdating = False
Run "ResetMenu"
Dim objTextBox As Object
With .CommandBars("Worksheet Menu Bar")
Set objTextBox = .Controls.Add(Type:=msoControlEdit, before:=.Controls.Count)
End With
With objTextBox
.Caption = "Custom Text Box"
.Width = 100
End With
.ScreenUpdating = True
End With
End Sub
Sub CustomtextBoxtext()
Dim tbVal$
tbVal = Application.CommandBars("Worksheet Menu Bar").Controls("Custom Text Box").Text
If Len(tbVal) = 0 Then
MsgBox "You did not enter anything in the text box.", 48, "Nothing to read !!"
Exit Sub
End If
On Error Resume Next
'Do whatever you want to that range
Range(tbVal).Select
If Err.Number <> 0 Then
MsgBox "You entered an invalid range reference in the menu bar text box." & vbCrLf & _
"''" & tbVal & "'' is not a valid range." & vbCrLf & vbCrLf & _
"Please enter a bona fide cell address or range.", 16, "No such worksheet range !!"
Exit Sub
Else
End If
End Sub
Step 4
Save the workbook.
Step 5
Close the workbook.
Step 6
Re-open the test workbook with the code you just installed, and notice:
- A message box greets the user with awareness of the textbox and how to enter a range in it (must hit the Enter key to confirm entry).
- Validation controls are in place to alert the user of they entered an invalid range or no text at all.
- You need to assign the CustomtextBoxtext macro to the Forms button on your sheet.
- Replace this code line
Range(tbVal).Select
with whatever action you want to take on that range, I only used Select for demo purposes.
Again, in my opinion, this is not an intuitive approach, which is why I added instructions in the message box, and validation alerts when the text is not entered with a recognizable range, but there it is 'cause you asked.
All the above steps worked for me, but it seems a lot easier to just enter with the keyboard, or select with the mouse (with or without the name box's help) the range you want to work with in an input box, than all this code for a textbox that will be visible all the time while the workbook is active, whose use is limited to this one task.
