Use an Input box to select a range

[Aussiebear]

I am looking for assistance to enable a User to set a range ( either Rows, or Columns, or a Used range) by way of an input box. The following code example is where a User can select a cell for a cell to carry out "number format" procedure. How should I alter this code?

Sub NumberFormatFromCell()
'PURPOSE: Obtain A Range From A Cell User's Determines
Dim rng As Range
Dim FormatRuleInput As String
'Temporarily Hide Userform
  Me.Hide
'Get A Range Address From The User 
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Range selection", _
      Prompt:="Select a range to reverse", _
      Type:=8)
  On Error GoTo 0
'Test to ensure User Did not cancel
  If rng Is Nothing Then
    Me.Show 'unhide userform
    Exit Sub
  End If
  'Set Variable to first cell in user's input (ensuring only 1 cell)
  Set rng = rng.Cells(1, 1)
 
'Store Number Format Rule
  FormatRuleInput = rng.NumberFormat
 
'Apply NumberFormat To User Selection
  If TypeName(Selection) = "Range" Then
    Selection.NumberFormat = FormatRuleInput
  Else
    MsgBox "Please select a range of cells before running this macro!"
  End If
 
'Unhide Userform
  Me.Show
 
End Sub

[Paul_Hossler]

enable a User to set a range ( either Rows, or Columns, or a Used range)

I think that's a little broad

Can you come up with some examples?

Some simple ideas

Option Explicit

Sub test()
'    MsgBox SetRange.Address     '   C:C
'    MsgBox SetRange.Address     '   3:3
'    MsgBox SetRange.Address     '   a1
    MsgBox SetRange.Address     '   b2:D5
End Sub

Function SetRange() As Range
Set SetRange = Nothing
On Error Resume Next
Set SetRange = Application.InputBox( _
     Title:="Range selection", _
     Prompt:="Select a range to reverse", _
    Type:=8)
On Error GoTo 0
'user selected Cancel
If SetRange Is Nothing Then Exit Function
If SetRange.Cells.Count = 1 Then Set SetRange = SetRange.CurrentRegion
End Function

If you wanted to allow more flexible inputs, you could have InputBox just return Text and then process it, something like

"1" = Rows(1)
"d" = ccolumns(4)
"3-5'' = Rows (3).Resize(3,)
"u" = .UsedRange
"ca" = .CurrentRegion

[Aussiebear]

I was thinking of using this in conjunction with my other current thread where the user might want to select;
1. rows 10 to 32, or
2. Columns A to G, or
3. Range C12: F17,
to conduct some form of operation on.

[Paul_Hossler]

If the user enters "10:32" or"A:G" of "C12:F17" without the quotes then I think my SetRange in #2 would do it

[Aussiebear]

Now the question remains as to how to get the user range into the other thread www.vbaexpress.com/forum/showthread.php?70845-Transposing-Data. My objective is to make that macro very functional by allowing the user to simply select their preferred range then carry out the operation.

[jolivanes]

Sub Like_This()
'<---- Thanks to RoyUK
Dim Rng As Range
Set Rng = Application.InputBox(Prompt:="Please select a range with dragging your mouse pointer", Title:="Selection required", Default:="Drag the mouse to make a selection.", Type:=8)
Rng.Value = "Hello"
End Sub

[Aussiebear]

Thank you Paul ad Jolivanes.

[Paul_Hossler]

Sub Like_This() '<---- Thanks to RoyUK Dim Rng As Range Set Rng = Application.InputBox(Prompt:="Please select a range with dragging your mouse pointer", Title:="Selection required", Default:="Drag the mouse to make a selection.", Type:=8) Rng.Value = "Hello" End Sub

Just be advised that there's no error or resonableness checking


If you [Cancel] you get an Object Required error


If you select an entire column or columns, then it goes everywhere. Same for entire row(s)