Left-align and right-align values in dynamic pop-up message (upon opening Excel)

Experts:

I would like some assistance with tweaking VBA output in a pop-up text box (upon opening Excel).

Please see attached XLS for details.    As depicted, the pop-up (data) is driven by values in the actual spreadsheet.   Right now, the labels (A1:A3) are automatically left aligned.   Values from column B, however, are not nicely aligned in the pop-up message.   That is, the two currency values (B1:B2) are pushed over due to vbTab.   And the % value is not aligned at all.

My question:   How can modify the VBA in order to "right-align" any values from column B in the pop-up message?

Thank you,
EEH
PopUpMessages.xlsm
ExpExchHelpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
No way to right align in a simple MSGBOX().  Here's a simplistic modification that improves things slightly, but not exactly what you are after...

Private Sub Workbook_Open()
        
    Dim strTxt As String
    strTxt = _
    Worksheets("PopUp Messages").Range("A1").Text & vbTab & Replace(Worksheets("PopUp Messages").Range("B1").Text, " ", "") & vbCrLf & _
    Worksheets("PopUp Messages").Range("A2").Text & vbTab & Replace(Worksheets("PopUp Messages").Range("B2").Text, " ", "") & vbCrLf & _
    Worksheets("PopUp Messages").Range("A3").Text & vbTab & Worksheets("PopUp Messages").Range("B3").Text
    MsgBox strTxt, vbOKOnly + vbInformation, "Pertinent Budget Data"
       
End Sub

Open in new window


»bp
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewCommented:
And you can right align everything as follows, but that just moves the problem I suppose...

   MsgBox strTxt, vbOKOnly + vbInformation + vbMsgBoxRight, "Pertinent Budget Data"

The typical approach to this is to create a very simple Form of your own in the Excel workbook, that provides the alignment flexibility you seek.  But naturally that is more work and maintenance.  If you want to take that approach, and need help with that just ask.


»bp
0
ExpExchHelpAuthor Commented:
That'll work!   Thank you for the feedback... I appreciate it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bill PrewCommented:
Welcome, glad that was some help.


»bp
0
ExpExchHelpAuthor Commented:
Bill - again, thanks.   If you happen to have a simple form (example) available, I'd welcome if you could please post it.

Thanks,
EEH
0
Bill PrewCommented:
Here's a quick sample of a userform approach.  Could be more elegant, but wanted to give you the idea...

PopUpMessages.xlsm


»bp
0
ExpExchHelpAuthor Commented:
Excellent example -- thousand thanks, Bill.

EEH
1
ExpExchHelpAuthor Commented:
Quick follow-up... I would like to apply conditional formatting to the % value field in the form (linked to cell B3).

I am using the following VBA [i.e., frmSummary!txtRight3.BackColor = RGB(0, 102, 0)] but it does seem to work yet (i.e., the msgbox indicating green, yellow, and red are only for testing):

Any ideas how I need to tweak the VBA >> frmSummary!txtRight3.BackColor = RGB(0, 102, 0) <<?

Thank you in advance,
EEH

    If Worksheets("PopUp Messages").Range("B3").Text < "70%" Then
        MsgBox "Green"
        
        'frmSummary.BackColor = RGB(0, 102, 0)                             ' Green
         frmSummary!txtRight3.BackColor = RGB(0, 102, 0)            ' Green .... I want to color either text field (background or font color) = green
               
    
    ElseIf Worksheets("PopUp Messages").Range("B4").Text > "70%" And Worksheets("PopUp Messages").Range("B4").Text < "75%" Then
        MsgBox "Yellow"
    
    Else
        MsgBox "Red"
    
    End If

Open in new window

0
ExpExchHelpAuthor Commented:
Attached is the sample file... press the control button "Run Pop Up Message".

Now, instead of coloring the entire form, I merely want to color the text field containing the % value.

Btw, use the drop-down in B4 to change from a few sample values driving colors green, yellow, or red.

How do I modify the VBA to change the color for the %?
PopUp-Messages-with-Data--with-cust.xlsm
0
Bill PrewCommented:
You can mess around with the colors, but here's a starting point.

Private Sub UserForm_Initialize()
    
    ' Populate the form fields from the worksheet data
    txtLeft1 = Worksheets("PopUp Messages").Range("A1").Text
    txtLeft2 = Worksheets("PopUp Messages").Range("A2").Text
    txtLeft3 = Worksheets("PopUp Messages").Range("A3").Text
    txtRight1 = Format(Worksheets("PopUp Messages").Range("B1").Value, "Standard")
    txtRight2 = Format(Worksheets("PopUp Messages").Range("B2").Value, "Standard")
    txtRight3 = Format(Worksheets("PopUp Messages").Range("B3").Value * 100, "Standard")
    
    ' Change color of percent field to indicate status
    Select Case Worksheets("PopUp Messages").Range("B3").Value
        Case Is > 0.75
            ' Red
            txtRight3.ForeColor = RGB(200, 0, 0)
        Case Is > 0.7
            ' Yellow
            txtRight3.ForeColor = RGB(200, 200, 0)
        Case Else
            ' Green
            txtRight3.ForeColor = RGB(0, 200, 0)
    End Select

End Sub

Open in new window


»bp
0
Bill PrewCommented:
Let me know if that gives you enough to get going, of if I can help more.  I posted before I saw your updated sample upload...


»bp
0
Bill PrewCommented:
If needed you can move that code to your click event of the sheet button you added.


»bp
0
ExpExchHelpAuthor Commented:
Most excellent!!! Again, thank you so much for your assistance.  :)
0
Bill PrewCommented:
Welcome.


»bp
0
ExpExchHelpAuthor Commented:
Bill... my apologies... final question:

When I move the file (e.g., into another subfolder) and then open the XLS (attached), I am prompted to "enable macros".   Once I do so, the XLS throws a run time error (see attached JPG).   Upon clicking "debug", the "frmSummary.Show" line is highlighted.

Private Sub Workbook_Open()
    frmSummary.Show      
End Sub

However, once I close/ignore the error, close XLS, and reopen the file, the error does not show up again.   Yet, I would like to make the error go away permanently.   What's missing/incorrect in the VBA that causing the run time error?

Thanks,
EEH
PopUp-Messages.xlsm
Error.JPG
0
Bill PrewCommented:
Will look at that in the morning...

~bp
0
ExpExchHelpAuthor Commented:
Thank you, Bill... I very much appreciate your continued assistance on this thread.
0
Bill PrewCommented:
I couldn't reproduce the problem here, but see if this adjustment works better.  I tidied up a few things in the code as well...

PopUp-Messages.xlsm


»bp
0
ExpExchHelpAuthor Commented:
Yes... I like the improvement.    In case of any other questions, I'll open a new thread and cross-reference URLs.  

Thank you, again.
0
Bill PrewCommented:
Welcome.


»bp
0
ExpExchHelpAuthor Commented:
Bill - I have opened a new thread.  

I would like to include a graph in the custom form (if that's doable).   Please see URL below for details.

https://www.experts-exchange.com/questions/29081649/Excel-custom-form-with-graph.html

Thank you,
EEH
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.