Restrict range in input box

[elm]

I have an input box that requires user input of a cell range. The columns are always the same eg. D:H. These columns cannot be varied. The row numbers do vary. Can anyone please tell me how to restrict entry to (for example) D50:H80 or D150:H250. Thanks in advance,

 

[Rick Rothstein]

I have an input box that requires user input of a cell range. The columns are always the same eg. D:H. These columns cannot be varied. The row numbers do vary. Can anyone please tell me how to restrict entry to (for example) D50:H80 or D150:H250. Thanks in advance,

Are you using the Application.InputBox (with the Type equal to 8) or are you using VB's InputBox where the user types in an address?

Also, if the user specifies an incorrect range, do you want the range automatically adjusted using the specified rows from the incorrect range or do you want an error message issued?

 

[elm]

Application.Inbox With Type 8. If incorrect range, an error message. Thanks

 

[Rick Rothstein]

Application.Inbox With Type 8. If incorrect range, an error message.

Structure your code similar to how I do it in the example code below...

Sub Test()
  Dim Rng As Range
  On Error GoTo NoRangeSelected
  Set Rng = Application.InputBox("Select range spanning Columns D:H.", Type:=8)
  If Rng(1).Column <> 4 Or Rng.Columns.Count <> 5 Then
    MsgBox "You selected range """ & Rng.Address(0, 0) & """ which does not span Columns D:H!", vbCritical
  Else
[COLOR="#008000"]    '
    ' The selected range is good, so do whatever you want with it here
    '[/COLOR]
  End If
  Exit Sub
NoRangeSelected:
  MsgBox "No range selected!", vbCritical
End Sub

 

[elm]

Thanks so much Rick. Exactly what I need. Thank you for taking the time to help me out
Regards, Rob