I have a map of a large building that was created using Excel (using shapes/lines etc.). I want to use this map such that the user can click this map and it return the building column number closest to the cell that the user clicked on. My VBA code behind it works fine, I use the Excel row and column where clicked to parse which building column is to be returned by the function - no problems here.
In order to make this work I'm using the Worksheet_SelectionChange() functionality to detect when the user selects somewhere on the map. This didn't work properly when all of the shapes/lines that constitute the map were in front of the cells that I wanted to click on. My solution to this was to export the map as a bitmap and use it as a background image. I made sure it was scaled exactly the same and was then able to click on any cell I want to without shapes interfering.
I placed transparent red rectangles on the areas of the map that the user should not click. By locking the worksheet, the user cannot select the rectangle objects and can only click on where I've defined clickable.
Now here's the tricky part: Everything is happy when I use it on my own computer. As long as I leave the worksheet scaling at 100%, the bitmap is perfectly lined up with my rectangles that are preventing the user from clicking where they shouldn't be. I sent it to a colleague, and the background was not scaled the same, even if he set it to 100%. Why is the scaling of my image changing? What can I do about it, from what I understand it's not possible to scale the background. Is there any way I can click "through" the shapes if I don't use the bitmap method?
I've checked other threads but most of the answers were simply "no". I am willing to do the vba coding required if there is some roundabout way to do this. I just don't have any ideas.
thanks.
Bookmarks