Consulting

Results 1 to 3 of 3

Thread: inputbox coding for invalid selection(s) - column

  1. #1
    VBAX Regular
    Joined
    Dec 2015
    Posts
    50
    Location

    Smile inputbox coding for invalid selection(s) - column

    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

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,910
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Dec 2015
    Posts
    50
    Location
    Ken,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •