Upon opening worksheet check value is present in userform

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,816
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

Well-known Member
Joined
Aug 31, 2016
Messages
2,919
.
Post you existing workbook to a cloud site so it can be downloaded for review.

Post the link here.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,919
.
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,816
Hi,
You are correct with the way it operates.

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

Thanks
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,919
.
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,816
.
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,816
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 ?


Code:
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

Well-known Member
Joined
Aug 31, 2016
Messages
2,919
.
This works here :

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,816
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


Code:
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
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top