Restrict user to select Entire row in Application.InputBox

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113
Dear Sir,

I want to restrict user from selecting entire row in an Application.InputBox. I don't want to password protect my worksheet.
I just want user to select a cell in A column only. That I have done. If user select any cell in any column except A column, it stops user from doing that.
But I can not restrict user to select entire row. Any help will be much appreciated!!

My code is as below

VBA Code:
Sub Copy_to_Sheet()
   
    Dim FromRange As Range
    Dim ToRange As Range
    Dim rownum As Long
    Dim Rng As Range
    Dim answer As Integer
   
    On Error GoTo Whoa
   
    rownum = InputBox("Enter Row Number to Copy.")
    Set Rng = Range(Cells(rownum, 1), Cells(rownum, 4))

    Set FromRange = Rng
   
    Sheets("Sheet2").Activate
    Set ToRange = Application.InputBox("Click on a cell to Paste : ", Type:=8)

'Selettion of Range for component in A column only
If ToRange(1).Column <> 1 Then
    MsgBox "You selected range """ & ToRange.Address(0, 0) & """ which does not span Columns A!", vbCritical
Else

    FromRange.SpecialCells(xlCellTypeVisible).Copy ToRange
   
    answer = MsgBox("Data changed on selected area.", vbOKOnly + vbExclamation, "Information")
End If
Whoa:
    Select Case Err.Number
        Case 13
           answer = MsgBox("This box can not be blank or You have clicked on 'Cancel' button. Operation Cancelled!!", vbOKOnly + vbExclamation, "Information")
            'Added for refreshing the screen
            Application.ScreenUpdating = True
        Case 1004
            answer = MsgBox("Entire row should not be selected. Try again by selection cells in A column only!!", vbOKOnly + vbExclamation, "Information")
            'Added for refreshing the screen
            Application.ScreenUpdating = True
    End Select

End Sub

Thanks in Advance
PritishS
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
If ToRange.Column <> 1 or ToRange.CountLarge>1 Then
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113

ADVERTISEMENT

You're welcome & thanks for the feedback.
Hi Fluff,

Actually in previous code I could select multi cells of A column in Application.InputBox by pressing Ctrl button. Also I could select ranges in A column. I needed that option. As per your suggestion, now I can only select one cell of A column.

Can you please suggest any modification.

Thanks for your valuable times.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
If Not Intersect(ToRange, Range("B:XFD")) Is Nothing Then
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top