Upon opening worksheet check value is present in userform

[ipbr21054]

Afternoon,

When i open my worksheet i see a msgbox asking if the userform should be opened.
Clicking on Yes should only open the form if NameForDateEntryBox.Text has a value in it.

Below is my code attemp but returns variable not defined thus i dont see this msgbox question

Private Sub Worksheet_Activate()
Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=14


Dim answer As Integer
 
answer = MsgBox("Open Postage User Form ?", vbYesNo + vbQuestion, "POSTAGE USER FORM MESSAGE")
If answer = vbYes And [COLOR=#ff0000]NameForDateEntryBox[/COLOR].Text = "" Then
Exit Sub
  PostageTransferSheet.Show
Else
  Exit Sub
End If
End Sub

 

[ipbr21054]

The logic of your desired action is flawed.

At that point, the only entry in the userform would be the default entries. "if there are more than 1 name" would never change.

I’ve been reading this again & looking at my form but don’t see an issue.

When the user opens the worksheet they are unaware if there is going to be 3 or 65 names in the userform drop down list.

The message box appears Would you like to open the userform. They select NO & the message box closes & they continue to work on the worksheet itself.

However if they had selected YES then the code should check how many names are in the drop down list BEFORE even thinking of opening the userform.
If there is 1 name or less then show message box No Namea etc, the message box closes & userform doesn’t open.

However if there are 2, 33 or 100 names then open userform.

I don’t see where the problem lies ???
Basically the code which we write will only open the userform if there are 2 or more names.
If they is 1 or less names then show message box with message etc.

What’s wrong with that please advise.

 

[Logit]

.
Post you existing workbook to a cloud site so it can be downloaded for review.

Post the link here.

 

[Logit]

.
Hopefully I have correctly understood what your macro code is doing
and the overall layout of your Form & Sheet.


Sheet POSTAGE, column G lists date items received. Those items that have
not yet been received are highlighted in RED.


If you write code to check for those RED colored cells ... that will
be the same as your desire to determine if the combobox NameForDateEntryBox
is populated.


Basically, if there are RED colored cells, that combobox will be populated.
If RED colored cells do not exist, then that combobox will NOT be populated.


You can approach this another way ... have your code check for EMPTY cells in
column G. That will also determine if the combobox will be populated with
names.

Hope this helps.

 

[ipbr21054]

Hi,
You are correct with the way it operates.

Can you advice something for checking on those empty cells etc.

Thanks

 

[Logit]

.

Sub redcellsfind()
Dim ws As Worksheet
Set ws = Sheets("Postage")
Dim i As Integer
i = 1
Do Until i = 2000 ' <-- change number rows to check here
    If ws.Range("G" & i).Interior.Color = RGB(255, 0, 0) Then
        MsgBox "There are empty cells."
        Exit Sub
    End If
i = i + 1
Loop
End Sub

 

[ipbr21054]

Hi,
This is for worksheet activate correct ?

 

[ipbr21054]

.

Sub redcellsfind()
Dim ws As Worksheet
Set ws = Sheets("Postage")
Dim i As Integer
i = 1
Do Until i = 2000 ' <-- change number rows to check here
    If ws.Range("G" & i).Interior.Color = RGB(255, 0, 0) Then
        MsgBox "There are empty cells."
        Exit Sub
    End If
i = i + 1
Loop
End Sub

Hi,

I have read / run this BUT i am told that there are empty cells via the message box ?

The goal for this code should be to tell me that there are no more customers names in the list etc.
This code advises me that cells which are red are empty.

 

[ipbr21054]

Hi,
Just an update & looking good but one more alteration & i think we are done.

Code in use is now shown below.
I see the message NO MORE CUSTOMERS IN LIST when there is 1 name left,can we edit so it appears only if 0 names apply ?

Private Sub Worksheet_Activate()
Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=14


Dim ws As Worksheet
Set ws = Sheets("POSTAGE")
Dim i As Integer
i = 1
Do Until i = 5000 ' <-- change number rows to check here
    If ws.Range("G" & i).Interior.Color = RGB(255, 0, 0) Then
        PostageTransferSheet.Show
        Else
        MsgBox "NO MORE CUSTOMERS IN LIST"
        Exit Sub
    End If
i = i + 1
Loop
End Sub

 

[Logit]

.
This works here :

Private Sub Worksheet_Activate()
Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=14




Dim ws As Worksheet
Set ws = Sheets("POSTAGE")
Dim i As Integer
i = 1
Do Until i = 5000 ' <-- change number rows to check here
    If ws.Range("G" & i).Interior.Color = RGB(255, 0, 0) Then
        PostageTransferSheet.Show
        Exit Sub
    End If
i = i + 1
Loop


MsgBox "NO MORE CUSTOMERS IN LIST"


End Sub

 

[ipbr21054]

The code below works fine BUT need to add msgbox advising no red cells are now present.
This then starts to create errors like mentioned above

Private Sub Worksheet_Activate()Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=14


Dim ws As Worksheet
Set ws = Sheets("POSTAGE")
Dim i As Integer
i = 1
Do Until i = 2000 ' <-- change number rows to check here
    If ws.Range("G" & i).Interior.Color = RGB(255, 0, 0) Then
        PostageTransferSheet.Show
        Exit Sub
    End If
i = i + 1
Loop
End Sub