Msgbox needs to be shown before textboxes are cleared

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,938
Office Version
  1. 2007
Platform
  1. 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.

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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,164
Office Version
  1. 2013
Platform
  1. Windows
@ipbr21054
I think you will find that, typically, it is the : TextBox1.Value = "" that is clearing the textboxes at the time of assigning value to the cells.
Remove them.
VBA Code:
With ThisWorkbook.Worksheets("Sheet3")
    .Cells(LastRow + 1, 1).Value = TextBox1.Text       '>>>>>: TextBox1.Value = ""  <<<<<
    .Cells(LastRow + 1, 2).Value = TextBox2.Text       '>>>>>: TextBox2.Value = ""   <<<<<

So then, the text boxes will retain their values and you will need to clear or re-set as you wish probably by adding to your final few code lines.

VBA Code:
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.SetFocus
TextBox1.Value = Now
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")

Note that your current code ^^^ sets TextBox1 to NOW but does nothing to TextBox2 !

Hope that helps.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,938
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I will remove the TextBox value = "" 1-6 as advised.
When i come to add a few lines at the end of my code to clear the contents etc how can i write it in one line as opposed to how its shown above on several lines.
Something like TextBox1-6.Clearcontents
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,164
Office Version
  1. 2013
Platform
  1. Windows
If clearing all 6 then like....
VBA Code:
'clear 6 text boxes
For i = 1 To 6
    Controls("TextBox" & i).Value = ""
Next i
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,938
Office Version
  1. 2007
Platform
  1. Windows
Morning,
Correct the advice you mentioned was the issue.
Ive now removed that & added something at the end of my code & works great.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,977
Messages
5,639,350
Members
417,082
Latest member
rbaltero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top