Running macro on selection of merged cell

[maiatanaka]

NOTICE: I know merged cells are horrible but I made a drop-down list so I merged the cells in order to allow the entire text show up when clicking on the drop-down button.

That being said, I'm trying to run a code that shows a shape box upon selection of cells. Using a code I found online, it works for every single cell but when I reach the merged cells, I can't seem to get the code to work. I've tried Target.Cells(1).Address(0,0); I've tried doing selection.count(2) alongside intersect; I've tried deeming ranges but nothing seems to work. Anybody have a clue about what I can do?

(I tried using center across selection but the problem is that the drop down button would then be out of place. This sheet is being used as a form sheet so resizing the columns also isn't an option at the moment...unless anybody has an idea of how I could arrange this?)

    If Selection.Count = 2 Then
        If Not Intersect(Target, Range("RentalSignature")) Is Nothing Then
        ActiveSheet.Shapes("RentalAgreementBox").Visible = True
    Else
        ActiveSheet.Shapes("RentalAgreementBox").Visible = False
    End If

 

[Zot]

What you meant by drop down button would be out of place?

 

[maiatanaka]

What you meant by drop down button would be out of place?

I just used a data validation drop down so if I were to just use one cell, the drop down menu doesn't show the entire text of the list. Instinctively too, I think if I were to not merge it, the text would continue to the second column (column F) but when you click on the cell, the arrow for the drop down should show half way where one would assume text to be present.

I thought about putting an actual drop down button from the developer tab but as you might see, I created it to use as a form and everything gets submitted to another sheet.


(Currently away from the desk but using Sheets on the phone as an example)

 

[maiatanaka]

Update: This is what I meant when I said when you don't merge the cells, it doesn't give you the complete list in the drop-down section which is why I feel like I have no other choice but to merge the cells. I could put in a drop down list through the developer tab but because I'm using this as a form and it gets inserted into another sheet, which at the same time you could also search for previous entries in the datasheet, using a button didn't seem to fit my needs.

vs

When I unmerge it, I don't have the problem of getting the box to show up. I'm currently using the simplified code:

  If Not Intersect(Target, Range("E18:F18")) Is Nothing Then
        ActiveSheet.Shapes("RentalAgreementBox").Visible = True
    Else
        ActiveSheet.Shapes("RentalAgreementBox").Visible = False
    End If

And it gets me the results I need. But once I merge them, it doesn't work.

Unmerged:

Merged:


(Replacing the E18:F18 with the merged cell name, which the range is correct as I have checked in the Name Manager, still doesn't work)

 

[Zot]

Not sure if I fully understand this. I tried to re-create the sheet but have no idea. The drop down sheet event triggered, right? Where is the validation list and how it is triggered. Maybe you can use XL2BB to copy paste the sheet so that I have a workable sample. I don't see why merge cell cannot work. Maybe I recreated something wrongly.

 

[maiatanaka]

Not sure if I fully understand this. I tried to re-create the sheet but have no idea. The drop down sheet event triggered, right? Where is the validation list and how it is triggered. Maybe you can use XL2BB to copy paste the sheet so that I have a workable sample. I don't see why merge cell cannot work. Maybe I recreated something wrongly.

No Zot, thank you for trying. Here's a sample file...I'm not too sure if it's also because I have other things that I'm asking it to do...(I'm still new to this)

 

[maiatanaka]

Not sure if I fully understand this. I tried to re-create the sheet but have no idea. The drop down sheet event triggered, right? Where is the validation list and how it is triggered. Maybe you can use XL2BB to copy paste the sheet so that I have a workable sample. I don't see why merge cell cannot work. Maybe I recreated something wrongly.

No, you're right! I actually had something wrong in my code. In my sheet, I also had another portion of the text that creates a shadow text in one of the boxes that I found the code online and in that code it had Target.Count > 1 Then Exit Sub which I didn't realize why that was why it wasn't working.

 

[Zot]

Glad you've found the culprit.