Commandbutton backcolor depends on caption

**serenitylai** · 04-15-2017, 10:49 AM

Hi, Everyone!

I'm working on a seat plan project wherein if button was clicked it'll turn to green if the seat was taken however I can't seem to figure out what to do with the update that I need to do.

Example:
Seat 354 was reported that there was an issue with headset it should turn YELLOW IF someone pressed on the seat 354 (if it was reported previously reported that there was an issue) a Msgbox should also show the issue it has however IF it was tagged as resolved already then the button will turn green again IF pressed.

I would really appreciate if someone will help me out.

**AlphaFrog** · 04-15-2017, 11:51 AM

Private Sub CmdGroup_Click()
    Dim rngIssue As Range, strIssue As String, strResolved As String
    With CmdGroup
        If .BackColor <> &H8000000F Then
            .BackColor = &H8000000F
        Else
            Set rngIssue = Sheets("Tracker").Range("B:B").Find(.Caption, , xlValues, xlWhole, 1, 1, 0)
            If Not rngIssue Is Nothing Then
                'There is an issue
                strIssue = Trim(StrConv(rngIssue.Offset(, 1).Value, vbProperCase))
                strResolved = rngIssue.Offset(, 2).Value
                If Trim(UCase(strResolved)) = "YES" Then
                    'Issue resolved
                    .BackColor = 50000
                Else
                    'Unresolved issue
                    Select Case strIssue
                        Case "Headset", "Mouse", "Migration Cable", "Keyboard": .BackColor = vbYellow
                        Case "Dongle", "System", "Monitor": .BackColor = vbRed
                    End Select
                    MsgBox "Issue: " & strIssue, , "Seat Issue"
                End If
            Else
                'No issue
                .BackColor = 50000
            End If
        End If
    End With
    UserForm1.CountCommandButton
End Sub

**serenitylai** · 04-15-2017, 12:10 PM

Hi!
I tried the code however it still stated the issue even though it was tagged as resolved ("YES")
THank you for your help!

**AlphaFrog** · 04-15-2017, 12:18 PM

Originally Posted by serenitylai

Hi!
I tried the code however it still stated the issue even though it was tagged as resolved ("YES")
THank you for your help!

It worked for me using your example workbook. Seat 151 had a resolved issue and didn't show a message.

**serenitylai** · 04-15-2017, 12:25 PM

it doesn't work on mine. Is it possible if it's due to the version of excel that I'm using?

**AlphaFrog** · 04-15-2017, 12:40 PM

It's not a version issue.

Are you using the same example workbook that you posted?
Did you change the code in any way?
Did you put YES in column D; not "Y" or "Yes " ?

**LeoTaxi** · 04-15-2017, 12:46 PM

Private Sub CmdGroup_Click()
Dim r As Range
With CmdGroup
    With Sheets("Tracker")
        Set r = .Cells.Find(CmdGroup.Caption, after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole)
        If r Is Nothing Then
            If CmdGroup.BackColor = 50000 Then CmdGroup.BackColor = &H8000000F Else CmdGroup.BackColor = 50000
            UserForm1.CountCommandButton
        Else
            CmdGroup.BackColor = r.Offset(, 1).Interior.Color
            MsgBox "Issue with " & r.Offset(, 1)
        End If
    End With
End With
End Sub

Private Sub CommandButton1_Click()
Dim NextRow As Integer, ldate As String, r As Range, mycolor As Long, r2 As Range
ldate = Format(Date, "mm/dd/yyyy")
If TextBox1.Value <> "" Then
    With Worksheets("Tracker")
        If ComboBox2 = "NO" Then
            NextRow = Application.CountA(.Range("A:A")) + 1
            .Cells(NextRow, 1) = ldate
            .Cells(NextRow, 2) = UserForm3.TextBox1
            .Cells(NextRow, 3) = UserForm3.ComboBox1
            With .Range("I:I")
                Set r2 = .Cells.Find(UserForm3.ComboBox1)
                mycolor = r2.Interior.Color
            End With
            .Cells(NextRow, 3).Interior.Color = mycolor
        ElseIf ComboBox2 = "Yes" Then
            Set r = .Cells.Find(TextBox1, after:=.Cells(1), LookIn:=xlValues, lookat:=xlWhole)
            .Cells(r.Row, 2) = UserForm3.TextBox1 & "  Resolved  " & ldate
            .Cells(r.Row, 3).Interior.Pattern = xlNone
        End If
    End With
    Unload Me
End If
End Sub

Kind regards
Leo

**myou** · 04-15-2017, 01:04 PM

Maybe?

Option Explicit
Option Compare Text
Private Sub CmdGroup_Click()
    Dim rngIssue As Range, strIssue As String, strResolved As String
    With CmdGroup
        If .BackColor <> &H8000000F Then
            .BackColor = &H8000000F
        Else
            Set rngIssue = Sheets("Tracker").Range("B:B").Find(.Caption, , xlValues, xlWhole, 1, 1, 0)
            If Not rngIssue Is Nothing Then
                'There is an issue
                strIssue = Trim(StrConv(rngIssue.Offset(, 1).Value, vbProperCase))
                strResolved = rngIssue.Offset(, 2).Value
                If Trim(UCase(strResolved)) = "YES" Then
                    'Issue resolved
                    .BackColor = 50000
                Else
                    'Unresolved issue
                    Select Case strIssue
                        Case "Headset", "Mouse", "Migration Cable", "Keyboard": .BackColor = vbYellow
                        Case "Dongle", "System", "Monitor": .BackColor = vbRed
                    End Select
                    MsgBox "Issue: " & strIssue, , "Seat Issue"
                End If
            Else
                'No issue
                .BackColor = 50000
            End If
        End If
    End With
    UserForm1.CountCommandButton
End Sub

**serenitylai** · 04-22-2017, 11:13 AM

sorry for the very late reply. Will tag this as solved. Thank you for the codes!

**myou** · 04-24-2017, 06:55 AM

Heelo serenitylai,

You are welcome, glad we help solve your issue.

Thank you for adding to my Reputation.

Cheers.

Commandbutton backcolor depends on caption

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Re: Commandbutton backcolor depends on caption

Thread Information

Users Browsing this Thread

Similar Threads

Problem Backcolor Commandbutton MAC

[SOLVED] CommandButton caption from a different workbook

[SOLVED] CommandButton caption set on open

CommandButton Caption Text Clipped

Commandbutton caption change

Commandbutton backcolor change on event

Caption disappears from CommandButton.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions