Capturing cell address through inputbox

Question

I am trying to capture the address of a user-selected cell through the use of the InputBox function for VBA in Excel 2003. In the following code, the CONTENTS of the cell are returned through the range variable "Delete_Rec_Range" instead of the ADDRESS in the "Set" statement below.

Sub Delete_Details() ' Dim Delete_Rec_Range As Excel.Range

' The following constants define the rows and columns in the Records_Table Const Rec_Start_Row = 2 Const Rec_Name_Col = 1 Const Rec_Group_Col = 2 Const Rec_Region_Col = 3

' 1. Select record to be removed ' a. Determine Records_Table range

   Rec_End_Row = Range("Records_Table").Rows.Count + Rec_Start_Row - 1

' b. Prompt user to select record to be removed

   On Error Resume Next
       Set Delete_Rec_Range = Application.InputBox("Click on the record to be removed from the table.", _
       "Click on Name", Selection.Address, , , , , 8)
       If Delete_Rec_Range = vbNullString Then
           User_Msg = MsgBox("Operation cancelled at your request.  No updates made.", vbInformation)
           Run_Flag = False
       Else...

Answer

I don't see anywhere that you try to get the address of the cell, but this is the way I would do it (I am not sure what you are looking for with the vbNullString test. That would only be true if the cell selected were blank - if cancel was clicked - it would raise an error which you suppress). Hopefully that will give you some insights. Applying On Error Resume Next to too broad a range of code can cause you to not understand what is happending - my personal opinion.

Sub Delete_Details() ' Dim Delete_Rec_Range As Excel.Range

' The following constants define the rows and columns in the Records_Table Const Rec_Start_Row = 2 Const Rec_Name_Col = 1 Const Rec_Group_Col = 2 Const Rec_Region_Col = 3

' 1. Select record to be removed ' a. Determine Records_Table range

  Rec_End_Row = Range("Records_Table").Rows.Count + Rec_Start_Row - 1

' b. Prompt user to select record to be removed

  On Error Resume Next
      Set Delete_Rec_Range = Application.InputBox( _
           "Click on the record to be removed from the table.", _
           "Click on Name", Selection.Address, , , , , 8)
  On Error goto 0
'      If Delete_Rec_Range = vbNullString Then
 if Delete_Rec_Range is nothing then
          User_Msg = MsgBox("Operation cancelled at your request.  No updates made.", vbInformation)
          Run_Flag = False
 Else
     if Delete_Rec_Range.Address = Selection.Address then
          msgbox "You chose to keep the default"
          exit sub
     Else
          msgbox "You selected cell " & Delete_Rec_Range.Address(0,0,xlA1,True)
     end if
 end if

Here is a test snippet:

Sub abc() Dim Delete_Rec_Range As Excel.Range

On Error Resume Next

     Set Delete_Rec_Range = Application.InputBox( _
           "Click on the record to be removed from the table.", _
           "Click on Name", Selection.Address, , , , , 8)

On Error GoTo 0 If Delete_Rec_Range Is Nothing Then

  MsgBox "You did not select a range - terminating"

Else

     If Delete_Rec_Range.Address = Selection.Address Then
          MsgBox "You chose to keep the default"
          Exit Sub
     ElseIf IsEmpty(Delete_Rec_Range(1).Value) Then
          MsgBox "You selected a blank cell at " & Delete_Rec_Range.Address(0, 0, xlA1, True)
     Else
          MsgBox "You selected cell at " & Delete_Rec_Range.Address(0, 0, xlA1, True) & _
          "Containing: " & Delete_Rec_Range(1).Text
     End If

End If End Sub

If that doesn't answer your question, post a followup with additional information.