Apply text to a cell userform to worksheet in current working code

[ipbr21054]

Good morning,

The code in use is supplied below.
Currently when i press my Postage Sheet Transfer Button the values are added to my worksheet and the cell in column G is applied the interior RGB colour Red.

Please can you advise how i also have text in the same cell, so not only is it then shown as RED but also the word IN POST applied.

Have a nice day.

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)
    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")
End Sub

 

[Logit]

.
I understood your question to ask ... 'How do I enter the word POSTED into the cell ? ' ....

Yes ?

 

[ipbr21054]

No,

The code looks for red cells in column G then these names are placed into the list box.

These red cells now show POSTED in them.

Because of the text POSTED in the cells the names are not loaded into the listbox so

The code MUST look for red cells & also allow the word POSTED then load names into listbox

Basically no text just red cells the names are loaded.
Red cells & text then no names are loaded.

So the code must allow text so it can continue to load the names

 

[Logit]

.
Give this a try :

If ws.Range("G" & i).Interior.Color = RGB(255, 0, 0) and ws.Range("G" & i).Value = "POSTED" Then

 

[ipbr21054]

Hi,

See code supplied.
When i open worksheet and see message do i wish to open userform i selected yes but i then see message no more parcels

Private Sub Worksheet_Activate()
Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=14


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
    
        ws.Range("G" & i).Value = "POSTED"
        PostageTransferSheet.Show
        Exit Sub
    End If
i = i + 1
Loop
[COLOR=#ff0000]MsgBox "NO MORE PARCELS ARE AWAITING DELIVERY", vbInformation, "POSTAGE DATE TRANSFER SHEET MESSAGE"[/COLOR]


End Sub

 

[Logit]

.
You also need to delete this line from the code :

[COLOR=#333333]ws.Range("G" & i).Value = "POSTED"[/COLOR]

So that portion of your macro will look like this :

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

 

[ipbr21054]

Same as before.
No names loaded into listbox if text in red cell.

 

[Logit]

.
Can you post your workbook on a cloud site so it can be downloaded ?

Provide the link here for the download.

 

[Logit]

.
When I open the sheet and click yes to see the userform .... the form loads correctly. I do not receive the message "no more parcels".

????

 

[ipbr21054]

do you see the names in the drop down list top right ?

 

[Logit]

The drop down CUSTOMER SEARCH has many, many listings.

The drop down NAME FOR DATE ENTRY has one name and one other line that says "Do Not Remove"