Return the Name/Caption and Color of a Command Button

[mitchbvi@mac.co]

I have developed code to build a dynamic calendar. One of the last things I need to do is return the name and color assigned to a command button on my worksheet. When I create the buttons the intention is the data on the button goes into the appropriate cell in the calendar and the color matches that of the text. However I cannot figure out how to return that information when I click on the button any suggestions please. I could type in the data again (as Ic an see it onthe button) but that is not efficent.

Peter:

[shg]

Like this?

Please Login or Register  to view this content.

[mitchbvi@mac.co]

I build the buttons dynamically and as I build them I assign a routine to them. That routine does not know what the name/caption of the command button is. So your suggestion will not work but thank you for the suggestion. Is there a way of writing a sub routine using code?

As I click the button I need to return the properties of that button I hope that does not sound stupid. If you would like me to post the code that builds the button please let me know.

Thanks

Peter

[shg]

Why go to the trouble of creating a command button in code, rather than creating a calender input in a cell, formatting as you wish, and pushing a button (or just double-clicking the cell) to move the text (and its formatting) to the calendar?

[mitchbvi@mac.co]

Because what I am doing is prpearing a calendar that records locations. So When I am asked for the data I also specify the duration and it fills in the data andf colors for "X" days and this changes all the time.

Regards

Peter

[shg]

I guess my question is, what additional information can you convey with whatever formatting and attribution you can apply to a command button that you might not equally convey via alternative and simpler means?

[mitchbvi@mac.co]

I think the best way to explain it is to describe the process.

I start with a blank calendar no dates no months just 12 boxes. I am asked when the calendar starts and it is not always the 1st January. This then creates a calendar with the dates in text boxes on the left side of each cell and of course the correct months.

Then I am asked what events or places I want to enter into the calendar. In answering this question and selecting a color it creates a button to the right of the calendar with the text on the button in the selected color.

Next is the process of filling in the calendar. I select what ever date is the start date then what I want to do is click on the appropriate button. What I want the click to do is return the text on the button and its color which will be used to fill in the number of days I have selected. In my case I use this calendar for places as it is important I keep track how long I am in each one.

In my earlier versions I built the buttons for all the places I thought I needed and assigned a separate sub routine to each one so that when I clicked on it I knew which button I was using.The subroutine had the text and color index and used that to complete the cells specified. Now I want to call the function that returns details from the button. Hopefully this is not too confusing.

I would be grateful for a hint as to another way of doing this. I have tried to use the rowsource command to get the data from a named range but as I am using Excel on a Mac for some reason this command does not work. I have looked at list and combo boxes but cannot figure out how to populate them.

Thanks for your help any other suggestions or ideas greatly appreciated.

Peter

[royUK]

It seems over complicated to me, can you attach a small example workbook?