Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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:
    <code>
    With ActiveSheet.Buttons("Button 1")
    Debug.Print .Caption
    Debug.Print .Font.Color ' or ColorIndex
    End With</code>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    And if they are from the Control Toolbox, you can use
    <code>
    With ActiveSheet.OLEObjects("CommandButton1").Object
    Debug.Print .Caption
    Debug.Print .ForeColor
    End With
    </code>
    or
    <code>
    With ActiveSheet.CommandButton1
    Debug.Print .Caption
    Debug.Print .ForeColor
    End With</code>

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Try

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

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  13. #13
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  14. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

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

    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

  15. #15
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Return Text on a Button & its Color (English/E

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •