ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,227
- Office Version
- 2007
- Platform
- Windows
Afternoon,
I have the MsgBox after theTextboxes are cleared where it should be before.
This is how it should work.
Open UserForm.
Enter values in textboxes
Press commandbutton called PostageSheetTransferButton
Msgbox question HAS SECURITY MARK BEEN APPLIED
Selecting YES would then clear the textboxes & add values to my worksheet.
Colour in cells would change on worksheet column D & G depending on Msgbox YES / NO question.
Msgbox MESSAGE TO CONFIRM Customer Postage Sheet Updated
The issue is that the Msgbox question HAS SECURITY MARK BEEN APPLIED needs to be shown when i press the command button called PostageSheetTransferButton Then continue with clearing textboxes & entering values to worksheet.
Ive tried what i thought was correct in moving the code before the textbox clear etc but after so many failed attempts & run time error messages i wrote this post as the errors would of been longer than what im writing now.
If you could advise,many thanks & have a nice day
Here is the working code in use at present.
I have the MsgBox after theTextboxes are cleared where it should be before.
This is how it should work.
Open UserForm.
Enter values in textboxes
Press commandbutton called PostageSheetTransferButton
Msgbox question HAS SECURITY MARK BEEN APPLIED
Selecting YES would then clear the textboxes & add values to my worksheet.
Colour in cells would change on worksheet column D & G depending on Msgbox YES / NO question.
Msgbox MESSAGE TO CONFIRM Customer Postage Sheet Updated
The issue is that the Msgbox question HAS SECURITY MARK BEEN APPLIED needs to be shown when i press the command button called PostageSheetTransferButton Then continue with clearing textboxes & entering values to worksheet.
Ive tried what i thought was correct in moving the code before the textbox clear etc but after so many failed attempts & run time error messages i wrote this post as the errors would of been longer than what im writing now.
If you could advise,many thanks & have a nice day
Here is the working code in use at present.
VBA Code:
Private Sub PostageSheetTransferButton_Click()
Cancel = 0
If TextBox2.Text = "" Then
Cancel = 1
MsgBox "Customer`s Name Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox2.SetFocus
ElseIf TextBox3.Text = "" Then
Cancel = 1
MsgBox "Item Description Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox3.SetFocus
ElseIf TextBox4.Text = "" Then
Cancel = 1
MsgBox "Tracking Number Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
TextBox4.SetFocus
ElseIf ComboBox1.Text = "" Then
Cancel = 1
MsgBox "Username Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
ComboBox1.SetFocus
ElseIf OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then
Cancel = 1
MsgBox "You Must Select An Ebay Account", vbCritical, "POSTAGE TRANSFER SHEET"
ElseIf OptionButton4.Value = False And OptionButton5.Value = False And OptionButton6.Value = False Then
Cancel = 1
MsgBox "You Must Select An Origin", vbCritical, "POSTAGE TRANSFER SHEET"
End If
If Cancel = 1 Then
Exit Sub
End If
Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.Count, 1).End(xlUp).Row
With ThisWorkbook.Worksheets("POSTAGE")
.Cells(LastRow + 1, 1).Value = TextBox1.Text: TextBox1.Value = ""
.Cells(LastRow + 1, 2).Value = TextBox2.Text: TextBox2.Value = ""
.Cells(LastRow + 1, 3).Value = TextBox3.Text: TextBox3.Value = ""
.Cells(LastRow + 1, 5).Value = TextBox4.Text: TextBox4.Value = ""
.Cells(LastRow + 1, 9).Value = ComboBox1.Text: ComboBox1.Value = ""
.Cells(LastRow + 1, 4).Value = TextBox6.Text: TextBox6.Value = ""
.Cells(LastRow + 1, 7).Interior.Color = RGB(255, 0, 0)
.Cells(LastRow + 1, 7).Value = "POSTED"
If OptionButton1.Value = True Then .Cells(LastRow + 1, 8).Value = "DR": OptionButton1.Value = False
If OptionButton2.Value = True Then .Cells(LastRow + 1, 8).Value = "IVY": OptionButton2.Value = False
If OptionButton3.Value = True Then .Cells(LastRow + 1, 8).Value = "N/A": OptionButton3.Value = False
If OptionButton4.Value = True Then .Cells(LastRow + 1, 6).Value = "EBAY": OptionButton4.Value = False
If OptionButton5.Value = True Then .Cells(LastRow + 1, 6).Value = "WEB SITE": OptionButton5.Value = False
If OptionButton6.Value = True Then .Cells(LastRow + 1, 6).Value = "N/A": OptionButton6.Value = False
Dim colorHTML As String, r As String, g As String, b As String
If MsgBox("HAS SECURITY MARK BEEN APPLIED ?", vbYesNo + vbExclamation, "PINK LIPSTICK MESSAGE") = vbYes Then
colorHTML = "FF0099"
r = WorksheetFunction.Hex2Dec(Left(colorHTML, 2))
g = WorksheetFunction.Hex2Dec(Mid(colorHTML, 3, 2))
b = WorksheetFunction.Hex2Dec(Right(colorHTML, 2))
.Cells(LastRow + 1, 4).Interior.Color = RGB(r, g, b)
End If
MsgBox "Customer Postage Sheet Updated", vbInformation, "SUCCESSFUL MESSAGE"
End With
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.SetFocus
TextBox1.Value = Now
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
Unload PostageTransferSheet
PostageTransferSheet.Show
End Sub