Running macro on selection of merged cell

maiatanaka

New Member
Joined
Dec 9, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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?)

VBA Code:
    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
 

Attachments

  • screenshot.png
    screenshot.png
    37.9 KB · Views: 10

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
What you meant by drop down button would be out of place?
 

maiatanaka

New Member
Joined
Dec 9, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Attachments

  • datavalidation merged.jpg
    datavalidation merged.jpg
    155.1 KB · Views: 6
  • datavalidation no merge.jpg
    datavalidation no merge.jpg
    141.2 KB · Views: 6

maiatanaka

New Member
Joined
Dec 9, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.

full list.png
vs
list.png


When I unmerge it, I don't have the problem of getting the box to show up. I'm currently using the simplified code:
VBA 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:
unmerged.png

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)
 
Last edited by a moderator:

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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. :unsure:
 
Solution

maiatanaka

New Member
Joined
Dec 9, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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. :unsure:
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

New Member
Joined
Dec 9, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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. :unsure:

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

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
Glad you've found the culprit. ;)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,127
Messages
5,835,553
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top