Textbox BackColor Problem

Pacman52

Active Member
Joined
Jan 29, 2009
Messages
319
Office Version
  1. 365
Platform
  1. Windows
Hi could someone please solve this one for me as I really am totally baffled and really don't know what is causing the issue!

So I have a textbox 'textboxSaleNotes' on a Userform coded as below

VBA Code:
Private Sub textboxSaleNotes_Enter()

    textboxSaleNotes.BackColor = RGB(204, 255, 255)
    
End Sub

Private Sub textboxSaleNotes_Change()

    textboxSaleNotes.Value = Application.WorksheetFunction.Proper(textboxSaleNotes.Value)

End Sub

Private Sub textboxSaleNotes_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    textboxSaleNotes.BackColor = RGB(255, 255, 255)
    
End Sub

This all works as expected if I test it and simply go into the box type something and then exit the box but the end user has to complete various other boxes first and dependent on their responses to some msgboxes they will eventually end up in the notes textbox (its the final box on the form).

The textbox is disabled on the form initialize and then enabled just after - again this all works as expected.

But for some reason as soon as the code below is run the notes textbox simply will not go to the backcolor I've set it to (blue). However if I comment out the setfocus bit it does but I need it to get the focus as well.

VBA Code:
Private Sub txtQty1_AfterUpdate()

    Dim MsgReply1 As VbMsgBoxResult
    Dim MsgReply2 As VbMsgBoxResult
    
    If IsNumeric(txtQty1) And txtQty1.Value >= 1 Then
    
        MsgReply1 = MsgBox("Do you want to enter another product?", vbQuestion + vbYesNo, "Add Next Product")
    
        If MsgReply1 = vbYes Then
        ComboProd2.SetFocus
    
    Else
    
        ComboProd2.Enabled = False
        txtQty2.Enabled = False
        
        MsgReply2 = MsgBox("Do you want to enter any notes about this record?", vbQuestion + vbYesNo, "Add Notes")
        
        If MsgReply2 = vbYes Then
        textboxSaleNotes.BackColor = RGB(204, 255, 255)
        textboxSaleNotes.SetFocus
    
    End If
    End If
    End If
    
End Sub

Can anyone see what I'm doing wrong or missing here?

Many thanks for and responses. Paul
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
VBA Code:
textboxSaleNotes.SetFocus

Is causing other events to fire, resetting the background color. Try putting the setfocus statement ahead of the statement that sets the background color.

VBA Code:
            MsgReply2 = MsgBox("Do you want to enter any notes about this record?", vbQuestion + vbYesNo, "Add Notes")

            If MsgReply2 = vbYes Then
                textboxSaleNotes.SetFocus
                textboxSaleNotes.BackColor = RGB(204, 255, 255)
            End If
 
Upvote 0
Hi thanks for the reply - unfortunately I'd already tried that and whilst it does set the focus to the box, the back color remains the same - 'white' even though the actual textbox is coded to change on enter.

I also tried just having the backcolor line on its own and that worked but when the setfocus and backcolor lines are both in the code regardless on which is first it just won't change the color to blue.
 
Upvote 0
it just won't change the color to blue.

I doubt that is the case. I think textboxSaleNotes.BackColor = RGB(204, 255, 255) is working normally and does change the color, otherwise nothing would happen when you remove .SetFocus. I suspect .SetFocus causes another event to trigger (most likely Sub textboxSaleNotes_Exit), and it is resetting the back color to RGB(255, 255, 255).

Another approach you can try:
VBA Code:
            If MsgReply2 = vbYes Then
                textboxSaleNotes.BackColor = RGB(204, 255, 255)
                Application.EnableEvents = False
                textboxSaleNotes.SetFocus
                Application.EnableEvents = True
            End If
 
Upvote 0
Solution
Thanks so much for the reply and alternative code. Reading your reply I thought I'd see what happens by just commenting out the notes exit sub first and for some reason it worked.

I'm really lost as to why this works though as I really can't understand how the exit sub conflicts or effects the response of the previous msgbox. But to be honest after spending hours trying to work this out I'm past the point of actually caring now as long as it works and I can move forward with this workbook,

Thanks again for taken the time to reply. Paul
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top