Can you advise a different way of this code to work please

[ipbr21054]

Morning,
Please see working code shown below.

I have a worksheet where i enter data and in column G, the word POSTED is shown & also RED.
Once the parcel has been delivered on my userform i select the customer and press the command button.

Now looking on my worksheet i see what was POSTED "red highlite" is now yellow and 16/02/2020 etc etc

So looking at my code each time i press the command button i see a msgbox "do you want to open the userform"
No closes the msgbox & i work on the worksheet.
Yes then runs the code to see if there are any POSTED in column G
Depending on the outcome the form opens OR i am told all parcels delivered and userfrom doen not open.

WOW.
What can we do so i dont see the question everytime DO YOU WISH TO OPEN THE USERFORM ?

I just seem to be pressing YES all the time

Private Sub Openuserform_Click()
Dim answer As Integer
answer = MsgBox("DO YOU WISH TO OPEN THE USERFORM", vbQuestion + vbYesNo + vbDefaultButton2, "POSTAGE OPEN USERFORM MESSAGE")
If answer = vbNo Then
  Exit Sub
Else
End If

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) And ws.Range("G" & i).Value = "POSTED" Then
        PostageTransferSheet.Show
        Exit Sub
    End If
i = i + 1
Loop
MsgBox "NO NAMES TO SHOW AS ALL PARCELS HAVE NOW BEEN DELIVERED", vbInformation, "POSTAGE DATE TRANSFER SHEET MESSAGE"

End Sub

 

[jasonb75]

Without an actual file to test the code on I can't follow the code step by step to find the error.

From a video, I can only see what it is doing wrong, not where, why or how.

As you said that the code in post 12 works perfectly it might be better to stick with that.

 

[ipbr21054]

iM WORKING ON IT NOW FOR YOU,FILE HERE SOON

 

[jasonb75]

I just noticed that fCell was not declared as static, that would be part of the problem, but I don't think that it is all of it.

Trying to follow the code mentally, I think that if this was the only problem then it should have found 'POSTED' on alternating clicks of the button (work on the first click, not on the second, work on the third, not on the fourth etc). So with that in mind, I don't think that this version will make much difference.

Private Sub Openuserform_Click()
Dim ws As Worksheet
Set ws = Sheets("POSTAGE")
Dim FirstRow As Long, LastRow As Long
Static FirstTime As Boolean, fCell As Range
If FirstTime = False Then Set fCell = ws.Range("G:G").Find(What:="POSTED", After:=ws.Range("G1"), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not fCell Is Nothing Then
    FirstTime = True
    FirstRow = fCell.Row
    LastRow = ws.Cells(Rows.Count, 7).End(xlUp).Row
    Do Until FirstRow > LastRow
        If ws.Range("G" & FirstRow).Interior.Color = RGB(255, 0, 0) And ws.Range("G" & FirstRow).Value = "POSTED" Then
            PostageTransferSheet.Show
            Exit Sub
        End If
    FirstRow = FirstRow + 1
    Loop
End If
    MsgBox "NO NAMES TO SHOW AS ALL PARCELS HAVE NOW BEEN DELIVERED", vbInformation, "POSTAGE DATE TRANSFER SHEET MESSAGE"
    FirstTime = False
    Set fCell = Nothing
End Sub

 

[ipbr21054]

While i look at the above i put back the original code as in post #1 as the issue is gone

 

[ipbr21054]

Reading your post #23 again you are correct.
With your code in place it did that.
Click all ok
Click msgbox
Click all ok
Click msgbox

 

[ipbr21054]

With your latest code in use it seems to have been fixed.
Each time i click open userform it opens.
I then click close userform.
I then click open userform it opens again.

With the previous code.
I could open the userform.
Close the userform
Then when i now click on open userform i would see the msgbox

This would then work on every one click