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 (http://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)