Commandbutton loses transparency

Hello

I have inserted a CommandButton from ActiveX controls in Sheet1 (Not in userforms). I have placed the button on cell A1 and made its backstyle transparent so that the value in A1 is always visible and user should not come to know that there is a button in there.

Now when I click it, it loses transparency. so what I did was, I wrote ActiveSheet.OLEObjects("CommandButton1").Object.BackStyle = fmBackStyleTransparent in the CommandButton1_Click() event but it did not help so I kept the code under GotFocus() event with no help.

How can I keep the transparency of the button permanent on all events (click, got/lost focus, mouse up/down etc?

Thank you.

To explain it better, I have attached a sample file. where I have taken two approaches.
1. There are four cells that takes a color when the cell is clicked. This is to show the user which cell is selected
2. There are four buttons (i have added only one button). When the user clicks on the button, it loses transparency.

I do not want to go with the first approach because, everytime a cell is clicked, the macro will run which will eat of system resources. That is why I wanted to go with the second approach but the problem is transparency and the look of the button. It should retain transparency so that the text behind the button is always visible and the button should be flat so that user should not come to know that there is a button in there. I can do this by changing the caption of the button on click but I do not want to go with that for some reasons.

I request some help here. Thanks

Hi freeofcost,
try this

Please Login or Register  to view this content.

It does not load the system
or use AutoShapes (see. the attachment)

Now thats what I call efficiency... Intersect is something I learned new today.
I am going with the AutoShapes approach.... thank you so much.

Hello,

I am running into one more problem. Please refer the revised attachment:
If cell E2 value is set to 'Yes', clicking any button (shape) colors the corresponding cell and re-click removes the color. I have added one more condition at cell F2. Present number is 1, if the number is changed to 2, it should allow the user to color two cells by clicking any two buttons and if the user clicks on third button, it should remove the color of the cell corresponding to the button the user clicked first.

I may have used a long method to achieve what I have done so far and some part is pending. There may be a better or efficient way of doing it. Please advice.

Thanks

Hi freeofcost,
try this

Please Login or Register  to view this content.

You know what! I was breaking my head over this since a couple of days. It is difficult for me to believe that this can be done in mere few lines of code. You rock man!

I am going to spend time on understanding how the code is getting executed and then going to come up with a different scenario and do it myself. I will come back to you if I need help. Thank you so much.

Originally Posted by freeofcost

I am going to spend time on understanding how the code is getting executed and then going to come up with a different scenario and do it myself. I will come back to you if I need help. Thank you so much.

What I understood so far is that the application.caller with an offset detects the cell and puts a number '1' in it and then the sum function is used to calculate how many cells contain number '1' and match with the condition in cell G2. Wow! thats a wonderful logic (I hope I have understood correctly)

Now if I move Shapes or put the buttons horizontally, the code stops working.... I guess because of SET offsets and the range Range("B2:B8"). I have changed the scenario a bit and altered the code. however, it does not work and only one column is getting selected. I think I have not understood the TopLeftCell property well.

Can you please have a look at the code? I have updated the attached file with the instructions. There are two sheets in it "Working" and "Not Working"

Thank you

Please Login or Register  to view this content.

Hi freeofcost,
look at the attached file

Hi freeofcost,
see attachment

Thank you Nilem,

1. I made a blunder in With(Range) and SUM range. Also, I must say that i need to practice offsets.
(a) Wanted to ask one more thing - Did you move or changed alignment of the shapes?


2. Regarding shape names, I wrote a similar code as you except for the visible property to false then why wasn't I getting the names?
(a) did you change the shapes properties somewhere or what exactly you did to make it work?
(b) Also, I am getting the names in some special characters. Any reason for this? Here are the names...

Прямоугольник 105
Прямоугольник 106
Прямоугольник 107
Прямоугольник 108

Your code

Please Login or Register  to view this content.

My Code

Please Login or Register  to view this content.

Your code (your "My Code") works for me. I got 79 names of shapes on the "ShouldWork" sheet.
So I deleted all the shapes and put 4 new rectangles. In the Russian version they take local names Прямоугольник (this is not the special characters, this is Russian language). You can change this name in the "Name" field (top left)

Originally Posted by nilem

Your code (your "My Code") works for me. I got 79 names of shapes on the "ShouldWork" sheet.

Surprisingly, my code did not produce result for me. I did got 80 names but all names were same... may be because i may have not named it on top left.

Originally Posted by nilem

In the Russian version they take local names Прямоугольник (this is not the special characters, this is Russian language).

I am so sorry... i never saw something like this in the past hence i thought it was special characters

Originally Posted by nilem

You can change this name in the "Name" field (top left)

I searched everywhere, format shapes, size and properties and dont even remember where but forgot looking at the top left... phew!

Thank you !!!