Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop to update form command buttons (Excel 2003)

    I'm not sure if what I'm thinking is possible but figured I'd ask the experts out there. I have a user form with multiple buttons that I need to update the fore color and back color. Since there are several buttons and multiple conditions that apply to each button in determining the correct colors to apply. I'm thinking I can right a for next loop that can loop through the command buttons but I do not recall the collection name and having trouble visualizing what this might look like. Below is some of what the code looks like and I've placed notes as to where the loop should begin and end.

    TD = Day(Now())
    TM = Month(Now())
    TY = Year(Now())

    If TM = 1 Then TM = "Jan"
    If TM = 2 Then TM = "Feb"
    If TM = 3 Then TM = "Mar"
    If TM = 4 Then TM = "Apr"
    If TM = 5 Then TM = "May"
    If TM = 6 Then TM = "Jun"
    If TM = 7 Then TM = "Jul"
    If TM = 8 Then TM = "Aug"
    If TM = 9 Then TM = "Sep"
    If TM = 10 Then TM = "Oct"
    If TM = 11 Then TM = "Nov"
    If TM = 12 Then TM = "Dec"


    ' Need to create loop here to loop through all command buttons
    ' in the user form.

    If CommandButton1.Caption = TD Then
    CommandButton1.BackColor = RGB(255, 255, 0) ' Yellow
    CommandButtton1.ForeColor = RGB(0, 0, 0) ' Black
    If ComboBox1.Value = TM Then
    If ComboBox2.Value = TY Then
    CommandButton1.BackColor = RGB(255, 255, 0) ' Yellow
    CommandButton1.ForeColor = RGB(255, 0, 0) ' Red
    End If
    End If
    Else
    CommandButton1.BackColor = RGB(0, 255, 255) ' Turquoise
    CommandButton1.ForeColor = RGB(0, 0, 0) ' Black
    End If

    ' Loop should end here

    Any assistance with this would be greatly appreciated. Thanks!!

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

    Re: Loop to update form command buttons (Excel 2003)

    In the first place, you can determine TM in one line like this:

    TM = Format(Date, "mmm")

    Here is a loop you can use as a starting point:
    <code>
    Dim ctl As Control
    For Each ctl In Me.Controls
    If TypeName(ctl) = "CommandButton" Then
    ' Code to manipulate the command button goes here, e.g.
    If ctl.Caption = TM Then
    ctl.ForeColor = vbRed
    End If
    End If
    Next ctl</code>

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop to update form command buttons (Excel 2003)

    Hans,

    Thank you very much for the prompt reply and the suggestion on the easier way to determine the variable TM. I will give this a shot and see what happens.

    Many thanks!!

Posting Permissions

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