I’ve read over the Microsoft explanation of Application.InputBox as well as some other websites, and from what I understand, type:=8 should return a range value, but only if you select multiple cells. Is there a way to get the range of a user selection for just a single cell?
Furthermore, I’ve used it in 2 situations and then used Intersect to make sure the user has selected a cell within a given range. The first use runs as intended, the second use produced “application defined or object defined error,” but the only differences between the two are the Application.InputBox title and the names of the variables set from Application.InputBox.
I have a feeling this error could be misleading, as there have been many times for example where I’ve forgotten to close an if statement but the error tells me the problem is with the while loop surrounding it. But I thought I’d put the problem up to see if anyone has an alternative method to obtain a single cell user selected range, and in the meantime I’ll look around the code of the second use to see if there is in fact something else that’s wrong.
First Use:
Dim shift_type_range As Range
Dim name As String
Set shift_type_range = Application.InputBox("Please select the shift type you would like to assign to " & name & "'s shift from the shift type key.", "Select Shift Type", Type:=8)
MsgBox shift_type_range ' outputs the value in the cell.
If Intersect(shift_type_range, Sheets("Rota Builder").Range("P21:P30")) Is Nothing Then ' If statement runs with no error
' the bulk in the if statement is irrelevant to the question.
End If
Second Use:
Dim staff_1 As Range
Set staff_1 = Application.InputBox("Please select the shift of the first member of staff.", "Select Shift", Type:=8)
MsgBox staff_1 ' Again, outputs the value in the cell.
If Intersect(staff_1, Sheets("Published Rota").Range("B7", "H" & b_last_row)) Is Nothing Then ' produced "application defined or object defined" error.
' the bulk in the if statement is irrelevant to the question.
End If
3
The Intersect method can works only if the two ranges are on the same sheet.
When your code execute the InputBox line, the default sheet is the active sheet. If you click a cell on it, and this sheet not corresponds to the other range’s parent sheet, an App runtime error is generated.
When you select the cell, first select the sheet (in the second case Published rota and then click on the cell you need.
The InputBox function works with one cell also. It is important that this is a Range object which is returned.
The b_last_row variable must contain a valid row value, otherwise an error is generated. (if <1, or greater then the max. number of rows of the excel application.
Balázs is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.