[VBA] Inputbox Method Type 8, but with ENTIRE ROW/COLUMN constraint

[oyy5408]

Hi,

I'm trying to prompt user to select cells, but only allow them to select entire column(or row), like the embedded options of "print titles".
I would like to be able to do this with inputbox, but if not possible, any other solutions are welcome.

Thanks!

 

[NeonRedSharpie]

Sub checkSelection()



    If Selection.Count = ActiveSheet.Columns.Count Or Selection.Count = ActiveSheet.Rows.Count Then
        MsgBox "WE ALL GOOD SON"
    Else
        MsgBox "TRY AGAIN"


    End If


End Sub

 

[Scott Huish]

I would just modify the selection after the fact.

Sub test()
Dim x As Range
On Error Resume Next
Set x = Application.InputBox("Select Row", Type:=8)
If x Is Nothing Then Exit Sub
Set x = x.EntireRow
x.Select
End Sub

 

[oyy5408]

Thanks for the codes, however, it is much more intuitive for the users if it worked EXACTLY like the "print titles" option, only allowing them to select by either rows or columns.
The cursor actually changes its icon to black arrow pointing to the right (something like this →) when I try to select the title rows, which is different from typical cell selector. Could that be a hint that Excel actually employs a different method instead of using "inputbox" method?

Thanks,

 

[Scott Huish]

I think you're thinking of a RefEdit Control on a UserForm. See here for information: XL2000: How to Use the RefEdit Control with a UserForm

You'll have to build a check in either way. YOu might also be able to take ideas from the code below.

However, here is some InputBox code that might help you.

Sub test()
Dim x As Range
Do
Set x = Application.InputBox("X", Type:=8)
Loop Until x.Columns.Count = Columns.Count Or x.Rows.Count = Rows.Count
End Sub

 

[Rick Rothstein]

I think you're thinking of a RefEdit Control on a UserForm. See here for information: XL2000: How to Use the RefEdit Control with a UserForm

You'll have to build a check in either way. YOu might also be able to take ideas from the code below.

However, here is some InputBox code that might help you.

Sub test()
Dim x As Range
Do
Set x = Application.InputBox("X", Type:=8)
Loop Until x.Columns.Count = Columns.Count Or x.Rows.Count = Rows.Count
End Sub

What about like this instead where if a partial range is selected, it is expanded in the natural direction...

Sub test()
  Dim Rng As Range
  On Error GoTo NothingSelected
  Set Rng = Application.InputBox("X", Type:=8)
  If Rng.Rows.Count > Rng.Columns.Count Then
    Rng(1).EntireColumn.Select
  Else
    Rng(1).EntireRow.Select
  End If
NothingSelected:
End Sub

 

[Scott Huish]

That's similar to where I was going with my first post, the op didn't like that idea.

 

[shg]

it is much more intuitive for the users if it worked EXACTLY like the "print titles" option, only allowing them to select by either rows or columns.

I think you'd need a RefEdit control on a userform to do that.

 

[oyy5408]

As suggested, I tried playing around with the RefEdit control, but it seems it is more trouble than it's worth, so I decided to use Scott's code above.

Thank you!

I think you're thinking of a RefEdit Control on a UserForm. See here for information: XL2000: How to Use the RefEdit Control with a UserForm

You'll have to build a check in either way. YOu might also be able to take ideas from the code below.

However, here is some InputBox code that might help you.

Sub test()
Dim x As Range
Do
Set x = Application.InputBox("X", Type:=8)
Loop Until x.Columns.Count = Columns.Count Or x.Rows.Count = Rows.Count
End Sub