Set range = application.inputbox reset

13 views Asked by At

I have a Set rng = application.inputbox that is inside a loop so if they select a wrong cell it asks again. My checks to acknowledge a "Cancel" or selection of multiple cells instead of one or selecting from the wrong Workbook work if they are selected the first time but not if it has looped due to the rng having data. I can't find a way to reset the rng or a better way to know if Cancel was pressed.

`    SelectAgain1:` 
`    On Error Resume Next`
`    Set rng = Application.InputBox("From the Current (or FIRST) BOM select the first data cell in the primary  
     Column (P/N or 12NC column)", "BOM 1 Selection for P/N, Column: " & cnt & ".", Type:=8)`
`    If rng = False Then 'THIS CHECK WAS ADDED TO SEE IF IT WOULD ACT DIFFERENTLY THEN THE NEXT CHECK.`
`        'CLEAR THE LINE IN THE LOG`
`        Workbooks(ToolLaunchWBname).ActiveSheet.Range("K6:P6").Delete Shift:=xlUp`
`        Workbooks(ToolLaunchWBname).ActiveSheet.Range("K6").Select`
`        Exit Sub 'EXITS IF THE CANCEL BUTTON WAS PRESSED`
`    End If`
    
`    If rng.Cells.Count < 1 Then 'CANCEL WAS SELECTED - CLEAR LOG ENTRY AND EXIT SUB`
`        Workbooks(ToolLaunchWBname).ActiveSheet.Range("K6:P6").Delete Shift:=xlUp`
`        Workbooks(ToolLaunchWBname).ActiveSheet.Range("K6").Select`
`        Exit Sub 'EXITS IF THE CANCEL BUTTON WAS PRESSED`
`    End If`

`    If rng.Cells.Count > 1 Then 'MAKES SURE ONLY ONE CELL WAS SELECTED`
`        ans = MsgBox("Please only select 1 cell.", vbCritical)`
`        GoTo SelectAgain1`
`    End If`

`    If rng.Parent.Parent.Name = ToolLaunchWBname Then` 
`        ans = MsgBox("You have selected a cell from the Tool Launch Workbook." & vbCr & _
        "Please select from a different Workbook.", vbCritical)`
`        GoTo SelectAgain1`
`    End If``


I tried:

`
rng.clear
rng = ""
set rng = ""
`

But they either error, or clear the cell value instead of resetting the variable.

0

There are 0 answers