How to ID cell location of object when clicked on?

[LvBombero]

I am creating an Excel form with multiple questions that typically have a Yes or No box (cell next to Yes and No). The box cells have an invisible object over them. The object has an assigned macro that places an "X" in the cell under the object and removes the "X" from the other cell (either Yes or No box).

I would like to create VB code that I don't have to adjust every time I move the question to a different row or column. If I knew the VB code that would identify the cell the object was clicked on, I could then use that info to fill the cell below.

Any help would be greatly appreciated.

Thank you

 

[Gary McMaster]

The sample below assumes your "invisible object" is a shape on which you could use "Application.Caller". All shapes can be assigned the same macro. The shape can be identified within the common macro by its name which is returned by Application.Caller

Hope this helps.

Gary

Public Sub Test()

MsgBox Application.Caller
MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
MsgBox ActiveSheet.Shapes(Application.Caller).BottomRightCell.Address

End Sub

 

[HalfAce]

Do you really need a separate object to click on for your yes & no entries?
You could do this in the Worksheet Selection, (or double click event), or something like that where all the user does is click on the cell to either insert or remove the yes and/or no.

Should be much quicker and easier to maintain if that's an option.

Sound like something worth looking at?

 

[Gary McMaster]

In addition to HalfAce's comments, you can also hyperlink (bookmark) a cell to itself and then use the "Hyperlink_Follow" event to identify the cell and run whatever code you need.

Gary

 

[LvBombero]

Thanks to all of you for all the great solutions. I'm going to try the Hyperlink first since I tried to utilize it in the beginning but did not know you can link to the same cell and run a macro. I'll see if I can figure it out but, may need some assistance.

Thanks again

 

[LvBombero]

OK, I NEED HELP. I tried all three solutions and I can not get any to work. I am definitely showing my lack of VBA skills. I definitely like the idea of not using an "Autoshape" over a cell and just having a macro run when certain cells are clicked on by the user (cells with borders and a "Yes" and "No" in the cell to the right).

Thanks again for any further help in advance.

 

[Gary McMaster]

I'm not exactly sure of what you're after but here's something to try. Maybe you can make it fit your needs. Please try it in a new workbook.

In a standard module:

Public Sub Test()

Dim oLink As Range
Dim oLinks As Range

Set oLinks = ActiveSheet.Range("A1:A20")

For Each oLink In oLinks
    oLink.Hyperlinks.Add oLink, "", oLink.Address, "", "Click Here (" & oLink.Address & ")"
Next oLink

End Sub

The "Workbook_SheetFollowHyperlink" event:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

Dim oFeedback As Range

Set oFeedback = Sh.Range(Target.SubAddress).Offset(0, 1)

Debug.Print oFeedback.Address

If oFeedback.Value = "Yes" Then
    oFeedback.Value = "No"
Else
    oFeedback.Value = "Yes"
End If

End Sub

Hope it helps.

Gary

 

[LvBombero]

Thanks Gary but, I'm still not getting it. I pasted your code into a new workbook and ran the "test" macro. It creates the 20 hyperlinks but not sure where to go from there.

I've created a question in a row with a "Yes" & "No" answer with bordered cells next to each. Example:

Q: Do you like computers? [_] Yes [_] No

I'd like to be able to move the question to any row or column without having to change the macro each time. My current macro looks like this:

Sub L_Yes()
'
' L row Yes box Macro
'
Range("L14").Select
If Range("L14").Value = "X" Then
Range("L14").Value = ""
Exit Sub
End If
Range("P14").Value = ""
Range("L14").Value = "X"
End Sub

I have an invisible autoshape over cells L14 that is assigned the above macro. I hope I made it a clearer.

Sorry for my lack of understanding on your solutions.

Thank you.

 

[HalfAce]

Assuming from your posted code that you're looking at columns L & P for entering the X, and you want to only allow one or the other to be chosen - here's something you can try.
Right click the sheet tab, choose View Code, copy this and paste it into the white area that is the sheet code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("L:L")) Is Nothing Then
  If Target = "" Then
    Target = "X": Target(, 5) = ""
  Else: Target = "": Target(, 5) = "X"
  End If
End If

If Not Intersect(Target, Range("P:P")) Is Nothing Then
  If Target = "" Then
    Target = "X": Target(, -3) = ""
  Else: Target = "": Target(, -3) = "X"
  End If
End If

End Sub

Press Alt+Q to close the vb editor and get back to your sheet to try it.
Now, select a cell in either column(s) L and P
That get you any closer to what you're wanting?

 

[Gary McMaster]

It sounds like whatever you do your "yes/no answer boxes" will have to be deleted and recreated on the fly with their positions based on the location of the question(s) if you intend to move the questions. Recreation will probably be easier than trying to move the "answer boxes" and keep them aligned with their original question. Especially if you need to keep track of which answer goes with which question?

I would be inclined to use a pair of framed option buttons for the yes/no indicators. The option buttons would take care of the mutually exclusive selection. Would that work for you?

If I could see your workbook maybe that would help. I've sent you a private message with my email address if you care to send a copy.

Gary