PDA

View Full Version : [SOLVED] inputbox coding for invalid selection(s) - column



SteveM99
08-01-2018, 07:45 AM
I need to modify my input box code for invalid selections. The user is prompted to select an amount from column "d" to then continue with the rest of the macro. I need to handle the issue when the user does not select an amount from column "d". Column D has mostly amounts but there are rows above the range which are not relevant so those could be excluded as well. I guess if there is a way to control the range of selectables. For example in column d, rows 7 though 25 may be the only valid selections. This will change based on other data from time to time but column d will stay the same for the curret input box.

So in a nutshell I need code that allows the user to select amounts (cells actually) only from a definable range in column "d". I you need please use rows 7 through 25 as an example and/or the additional code to use in getting that range into the macro when executed as it changes from time to time. Below is what I have which basically allows the user to select any cell which I want to limit to variable defined range in column "d".

'SET UP INPUT BOX
Dim DLOOKUP As Range
On Error Resume Next
SelectProcess:
Set DLOOKUP = Application.InputBox("Select AMOUNT FROM COLUMN D", Type:=8)
If DLOOKUP Is Nothing Then
MsgBox "Cell not selected!"
Exit Sub
ElseIf DLOOKUP.Count > 1 Then
MsgBox "More than one cell selected. Select One Cell!"
GoTo SelectProcess
Else

Kenneth Hobs
08-01-2018, 11:07 AM
Sub Main()
'SET UP INPUT BOX
Dim DLOOKUP As Range
On Error Resume Next
SelectProcess:
Set DLOOKUP = Application.InputBox("Select AMOUNT FROM COLUMN D", Type:=8)
Select Case True
Case DLOOKUP Is Nothing
MsgBox "Cell not selected!"
Exit Sub
Case DLOOKUP.Count > 1
MsgBox "More than one cell selected. Select One Cell!"
GoTo SelectProcess
Case Intersect(DLOOKUP, [D7:D25]) Is Nothing
Exit Sub
Case Else
MsgBox DLOOKUP.Value
End Select
End Sub

SteveM99
08-03-2018, 07:13 AM
Ken,

Thank you for your recommendation. I altered slightly for my additional needs and it worked like charm. I appreciate your help.