ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,731
- Office Version
- 2007
- Platform
- Windows
Afternoon,
I have in use the following code which transfers data from my userform to my worksheet.
Before i see the message "Customer Postage Sheet Updated" i am looking for a Yes / No Msgbox.
Where if No is selected then continue to show the "Customer Postage Sheet Updated" as normal
BUT
If Yes is selected then the background of the cell where the value from the userform texBox6 is going to be placed should then be an interior colour of #FF69B4 "Hot Pink" I would prefer to use the HTML code.
Then continue to show the "Customer Postage Sheet Updated" as normal
My effort would be as follows but having issues,
cOULD YOU PLEASE ASSIST,MANY THANKS
I have in use the following code which transfers data from my userform to my worksheet.
Before i see the message "Customer Postage Sheet Updated" i am looking for a Yes / No Msgbox.
Where if No is selected then continue to show the "Customer Postage Sheet Updated" as normal
BUT
If Yes is selected then the background of the cell where the value from the userform texBox6 is going to be placed should then be an interior colour of #FF69B4 "Hot Pink" I would prefer to use the HTML code.
Then continue to show the "Customer Postage Sheet Updated" as normal
Code:
Private Sub DateTransferButton_Click()'Dantes code
Dim sh As Worksheet
Dim b As Range
Dim wName As String, res As Variant
If NameForDateEntryBox = -1 Then
MsgBox "Please Select A Customer", vbCritical, "Delivery Parcel Date Transfer"
Exit Sub
End If
If TextBox7.Value = "" Or Not IsDate(TextBox7.Value) Then
MsgBox "Please Enter A Valid Date", vbCritical, "Delivery Parcel Date Transfer"
TextBox7 = ""
TextBox7.SetFocus
Exit Sub
End If
wName = NameForDateEntryBox.List(NameForDateEntryBox.ListIndex)
Set sh = Sheets("POSTAGE")
Set b = sh.Columns("B").Find(wName, LookIn:=xlValues, lookat:=xlWhole)
If Not b Is Nothing Then
If sh.Cells(b.Row, "G").Value <> "" Then
MsgBox "DATE HAS BEEN ENTERED ALREADY !" & vbCrLf & "Click OK To Go Check It Out ", vbCritical, "Delivery Parcel Date Transfer"
TextBox7 = ""
Unload PostageTransferSheet
Cells(b.Row, "G").Select
Else
sh.Cells(b.Row, "G").Value = CDate(TextBox7.Value)
MsgBox "Delivery Date Updated", vbInformation, "Delivery Parcel Date Transfer"
End If
End If
NameForDateEntryBox = ""
TextBox7 = ""
TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub
Private Sub DHLButton_Click()
ActiveWorkbook.FollowHyperlink Address:="http://www.dhl.co.uk/en/express/tracking.html", NewWindow:=True
End Sub
Private Sub HERMESButton_Click()
ActiveWorkbook.FollowHyperlink Address:="https://www.myhermes.co.uk/tracking-results.html", NewWindow:=True
End Sub
Private Sub labelsbUTTON_Click()
TrackingLabel.Show
End Sub
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 = ""
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
[COLOR=#ff0000] NEW MSGBOX HERE[/COLOR]
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")
End Sub
My effort would be as follows but having issues,
Code:
If OptionButton6.Value = True Then .Cells(lastrow + 1, 6).Value = "N/A": OptionButton6.Value = False
If MsgBox("SECURITY MARK APPLIED", vbYesNo + vbQuestion) = vbYes Then
[COLOR=#ff0000]**Not sure about TextBox6 & Pink code**[/COLOR]
Else
End If
MsgBox "Customer Postage Sheet Updated", vbInformation, "SUCCESSFUL MESSAGE"
cOULD YOU PLEASE ASSIST,MANY THANKS