Return Text on a Button & its Color (English/Excel/VBA 11.2)

Topic

New Reply

I have created code to make a calendar dynamically starting at any date. This calendar has a number of buttons, which I use to fill in data. So far I have created the data name and color for each event attached to buttons on one side of the calendar.

What I now want to do is create the buttons dynamically so that after selecting the appropriate dates I click on the button it changes the color background and enters the data. I can create the buttons and I set the color of the text on the button to the one I want on the calendar. However I cannot now figure out how to return the text on the button and the color of the text when I click on it. Any suggestions please.

Peter

Reply | Quote

Replies

Have you used command buttons from the Forms toolbar or from the Control Toolbox?

Added:

If they are from the Forms toolbar, you can use code like this:

With ActiveSheet.Buttons("Button 1")
Debug.Print .Caption
Debug.Print .Font.Color ' or ColorIndex
End With

Reply | Quote

And if they are from the Control Toolbox, you can use

With ActiveSheet.OLEObjects("CommandButton1").Object
Debug.Print .Caption
Debug.Print .ForeColor
End With

or

With ActiveSheet.CommandButton1
Debug.Print .Caption
Debug.Print .ForeColor
End With

Reply | Quote

Hi Hans

Thank you for the very prompt response. Unfortunately what I am trying to do is return the name and or caption of the button I click on so (“commandbutton1”) does not work, that is what I want to know.

What i do is to select a cell on the worksheet that represents my calendar. The code then determines which cell it is in and what the date is in a box that is in that cell. I then want to click on the command button to enter the data in that cell and also the color. So it is as I click the command button that I need to determine its properties. I should have mentioned that I am presently using Excel in a Mac environment do not know if that makes any difference. I tried using rowsource and a range in the workbook to provided the data but that does not seem to work in the Mac version.

To make sure I am not being too confusing each cell in my calendar has a small text box in it that holds the date, so what I enter in the cell itself is the data which comes (I hope) from the button and also use the color index of the text to change the background color of the cell. As I aid the calendar works fine if I build all the possible data names and assign colors before I start updating the calendar.

Thanks again

Peter

Reply | Quote

From the name “commandbutton1” I assume that you used commandbuttons from the Control Toolbox. You have a separate On Click event procedure for each such button. In these procedures, you know which button has been clicked, so I don’t see the problem.

I have no experience with recent versions of Excel for Apple Macintosh, so I cannot tell you whether it acts differently from Excel for Windows.

Reply | Quote

Hi Hans

I apologise I have not been very clear. I actually build each button with code as follows.

Sub Createbutton() ‘to work out cell position in twips
co = ActiveCell.Column ‘Locate Column Number
ro = ActiveCell.Row ‘Locate row number
‘Determine size and position of Cell
CurX = ActiveSheet.Cells(ro, co).Left
lt = CurX
CurX = ActiveSheet.Cells(ro, co).Width
wd = CurX
CurX = ActiveSheet.Cells(ro, co).Height
ht = CurX
CurX = ActiveSheet.Cells(ro, co).Top
tp = CurX
btntext = InputBox(“Enter the Name or Place for this Activity Select OK do not press Enter”) ‘calls for new name
ActiveSheet.Buttons.Add(lt, tp, wd, ht).Name = btntext
ActiveSheet.Shapes(btntext).Select ‘selects button just created
Selection.Characters.Text = “” ‘zeros text
Selection.Characters.Text = btntext
Selection.Characters.Font.Size = 10
ActiveCell.Value = btntext
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = btntext
co = ActiveCell.Column ‘Locate Column Numner
ro = ActiveCell.Row ‘Locate row number
SelectColor.Show ‘Calls a user form for color selection
End Sub

So they are command buttons. after I have build all I need I then select a position on the worksheet that corresponds to the required date. I then want to click on one of the command buttons I just created to have the data (which is the command buttons name/caption) entered in that cell. To do that I need to return the name of the button I have clicked and that is what I cannot do. I cannot use selection because the cell that is selected is still the one in the calendar not the one on which the command button rests not I suppose that is relevant. I need to return the properties of the button I have clicked.

Thanks again

Peter

Reply | Quote

Application.Caller is the name of the button clicked by the user.

Reply | Quote

Hans

Thats great I can now return the Name of the Button, but I am still very dense as I cannot figure out how to return the color of the font for that name can you help please. TypeName returns a lot of info but not the color.

Thansk

Peter

Reply | Quote

Try

ActiveSheet.Buttons(Application.Caller).Font.Color ‘ or ColorIndex

Reply | Quote

Hans

Have not tried to write the rest of the code but that looks like it . Thank you so much for your patience.

Take care

Peter

Reply | Quote

Hans

A quick note to thank you for all your help my code is now working. I also ran it in a Windows environment and it worked OK. I have one small problem more of a quirk I think. I use the calendar to keep track of places I am in. For some reason there are three places Vail, LA and CH that will not color the interior correctly. They always come up a light yellow. The text on the button is the correct color and when I insert a message box just before the interior color is changed I get the right index number. It happens in Windows and Mac so I assume it is not an OS problem.

I have attached a file which is the code used to create the buttons and then to enter the data in the calendar.

My apologies for bothering you again but this is the one place I seem to get help. Therefore one last question if I may. To set up the template for my calendar I had to enter text boxes in all the cells that may require them to hold the date. I could not find a way to do that within Excel and what I did was create a textbox in word copy it and paste it into excel and then copied that to each cell. This seems very crude and I wondered if I am missing something.

Thanks again Peter

Reply | Quote

I don’t understand what you need text boxes for. And I have no idea where your code goes wrong. Could you attach your workbook?

Reply | Quote

Hans

Tried to attach the work book and I am told the file is too big. Can I juts paste the code into a text file or do you need the workbook structure.

thanks

Pete

Reply | Quote

Office files tend to be very compressible. Try zipping up your .xls into a ZIP archive and posting that.

Reply | Quote

Hans

I need the text boxes so I can get two things in a cell the date (in the text box) and the place which is entered programatically.

Peter

Reply | Quote

At jescher200 suggestion I zipped the file should have thought of that it is attached.

Peter

Reply | Quote

I can’t make sense of the SelectColor form. Does it look radically different on a Mac than on Windows?

Reply | Quote

No it does not. I made the color palette by filling a number of cells on an excel worksheet with the colors I wanted then I captured that and posted it as an image to the SelectColour form. The numbers on the captured image are those that correspond to the colorindex.. The option buttons as you can see pass the number of the colorindex to the text on the button when clicked .

I did have a problem with the buttons at one point as I originally had a lot more colors and when I changed I failed to delete all the buttons and I had overlapping ones but that is now fixed.

I should mention that my windows check was done by running the windows version of excel from within a virtual machine (Parallels in fact). However it it is the Windows XP OS.
Thanks again

Peter

Reply | Quote

I’m afraid I cannot help, this is beyond me. Sorry.

Reply | Quote

In Windows, on Excel 2003, the radio buttons and the image are not aligned, or should I say the radio buttons extend above and below the image. If you had scaled the image to fit the height of the buttons, it appears that Windows does not handle that very well.

I’m not sure exactly how to use the sheet, but I ran ShowBaseData to create a 2007 calendar, clicked in November 2, then ran CreateButton. The button does appear over November 2nd (the alignment is slightly to the left of the cell boundary), but now November 16 is selected in the sheet. When I click the button, it refers to November 16th. If I enter “1”, it inserts some text and color into the November 16th cell and moves the selection to November 17th.

Anyway, to answer the question about vail and ch, your code is working. However, the setting under Format>Conditional Formatting… is overriding your assigned color.

Reply | Quote

Thank you very much you are correct but I cannot get rid of the conditional formatting. That by the way arose from an earlier addition of this project and I should have remembered it. In trying to fix the problem myself I rebuilt the model from scratch and it still brought the conditional formatting with it.

I apologize that my model was not well documented so you could experiment with it properly best I walk though those steps.

The model is set to open at what is called the face page that was to be a splash when I got everything else fixed. The button takes you to the calendar and asks you to save the model to avoid corrupting the template.

The calendar should be clear and you first need to build a calendar either for a split or calendar year. This inserts the correct dates into the text boxes.

Then you need to define places (or what ever) in my case always places. You do this by clicking on the large button to he right of the calendar having first selected a cell below that box. Any suggestion as to how I line the result box up with the cell would be appreciated. Having entered the name a form appears with a color chart selecting the color applies it to the text in the box.

Then click first on a date followed by one of the buttons it will ask you how many day this entry is for and fill in the text and the interior color of the cell.

Having received your note I checked on conditional formatting and sure enough you were correct so I deleted those. Then tried again, it fills in one cell with the correct color then defaults to the color specified under conditional formatting. That is now my problem I had deleted conditional formatting and it comes back. Have searched my code for anything that looks like conditional formatting without result. Have of course deleted conditional formatting and saved the workbook but it comes back. I have tried changing the conditions it works for the first cell in the sequence then reverts to what was there before. Tried copying the calendar sheet to a new sheet again works for one cell then conditional formatting is back. One thing I noticed is I copied the entire calendar sheet and pasted it to a new workbook the conditional formatting came with it if however I only copied all the cells with data in them it did not but once I tried to run the code it came back.

So now I am stuck I know the problem (thanks to your advice) but not how to fix it can you help.

Thanks

Peter

Reply | Quote

I actually know very little about Excel behavior, so I assumed that deleting the conditional formatting would be a global solution. Is it possible that your code is adding it back somehow?? Maybe post a populated version of the workbook so people can inspect the formatting without having to generate their own calendar.

Reply | Quote

Hi

I assumed it would fix it to but unfortunately not so. I assume my code (or something is adding the conditional formatting back ) as I mentioned it does one cell (the first one correctly) and then back comes the formatting. There is and if then sequence after you have selected a date you are then asked for how long and it process one cell at a time for the number of days specified. I have stepped though the code but I am missing it.

I have at your suggestion already posted the zip file of the whole thing and now with the instructions I provided in my last post I would think anyone could create a calendar. Are you suggesting something else happy to do what you recommend.

thanks

Peter

Reply | Quote