I am using a UserForm to do a validation of what might be incomplete on a worksheet. I am trying to get the different labels to appear in the appropriate next location and trying to use a count to do it. So that if label 1 visible and label 3 visible, there is no blank space between them where label 2 would be if it needed to be visible. Short piece of not working code below - there is a potential for 6 labels.
Dim lTop As Double lCount = 0 If lCount = 1 Then lTop = 6 If lCount = 2 Then lTop = 18 If lCount = 3 Then lTop = 36 If lCount = 4 Then lTop = 42 If lCount = 5 Then lTop = 60 If lCount = 6 Then lTop = 78 If lCount = 7 Then lTop = 96 If Sheet2.Range("ClientTypeVal").Value >= 1 And Sheet2.Range("ClientTypeVal").Value <= 5 _ And Sheet2.Range("FeeValue").Value < 1 Then lCount = lCount + 1 UserForm1.lblFee.Top = lTop UserForm1.lblFee.Caption = "• Please select a fee range." Else: UserForm1.lblFee.Caption = "" End If If Sheet9.Range("Ptnr").Value = "" Or Sheet9.Range("SigDate").Value = "" Then lCount = lCount + 1 UserForm1.lblPtnr.Caption = "• Please sign and/or date." UserForm1.lblPtnr.Top = lTop Else: UserForm1.lblPtnr.Caption = "" 'reg ldr same as partner If Sheet9.Range("Ptnr") = Sheet9.Range("Ldr") Then lCount = lCount + 1 UserForm1.lblRegIsPtnr.Caption = "• Please select an alternate Regional Leader." UserForm1.lblRegIsPtnr.Top = lTop Else: UserForm1.lblRegIsPtnr.Caption = "" End If End If
Last edited by ker9; 02-10-2012 at 12:18 PM.
Do you really need to use labels? How about using a list box then you simply additem if caption text is to be displayed.
Currently your code only has lTop set once and not incremented if a label contains text.
Hi, Andy
Thank you for your response. I never thought to use a list. That might be a better idea.
I did finally get my code to work - see below
Dim lTop As Double lcount = 0 lTop = 10 'check for fee If Sheet2.Range("ClientTypeVal").Value >= 1 And Sheet2.Range("ClientTypeVal").Value <= 5 _ And Sheet2.Range("FeeValue").Value < 1 Then UserForm1.lblFee.Top = lcount + lTop UserForm1.lblFee.Caption = "• Please select a fee range." lcount = lcount + lTop + 18 Else: UserForm1.lblFee.Caption = "" End If 'WP Index Cell should start with TWO (2) spaces in it for validation purposes. If (Sheet2.Range("ClientTypeVal").Formula = "2" Or Sheet2.Range("ClientTypeVal").Formula = "4") And Sheet9.Range("PcaobWpIndex").Value = " " Then UserForm1.lblWpIndex.Caption = "• Please enter PCAOB WP Index." UserForm1.lblWpIndex.Top = lcount + lTop lcount = lcount + lTop + 18 Else: UserForm1.lblWpIndex.Caption = "" End If 'check that partner signed and dated If Sheet9.Range("SigEngPtnr").Value = "" Or Sheet9.Range("EngPtnrSigDate").Value = "" Then UserForm1.lblPtnr.Caption = "• Partner needs to sign and/or date." UserForm1.lblPtnr.Top = lcount + lTop lcount = lcount + lTop + 18 Else: UserForm1.lblPtnr.Caption = "" End If
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks