ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,602
- Office Version
- 2007
- Platform
- Windows
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
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
VBA Code:
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