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

 

[ipbr21054]

Do this as its not search.
Top right of form you will see name for date entry.
Select a name & then select date transfer button.

On the worksheet you will see a few red cells.
These red cells are parcels awaiting delivery.

So at present red cells allow the names to be entered into the name for date entry field BUT type posted in the red cell then see what happens when you open the worksheet again

 

[Logit]

I used Harry Hunt found on line 995. That was the only name in the drop down.

After clicking Date Transfer Button todays date appears in Col G on row 995.

Typing POSTED into that same cell (G995) does not cause any errors or error messages.

 

[ipbr21054]

Look at the worksheet.
The red cells should be as follows.
ALEX RIMPAS 001 AND 002
RAMON
HARRY HUNT

I get the issue every time so its because you are new to how it works.
Follow these steps closely please.
Red cells without text.
Save and close worksheet.
Now open worksheet.
Select POSTAGE tab
Select yes to message box.
You will see no more parcels awaiting delivery message.
So click ok.

NOW DO THIS.

The red cells type POSTED in them.
Save and close workbook.
Open workbook.
Select POSTAGE TAB
Select yes to message.
Now the userform will open.
Now look in drop down list and you will see no names.
This is because of the text in the red cells.

each change i made i then saved & closed the worksheet as it gave me false info otherwise

 

[Logit]

.
I followed your directions to the letter.

The workbook here does things differently at the point outlined below :

The red cells type POSTED in them.
Save and close workbook.
Open workbook.
Select POSTAGE TAB
Select yes to message.

Now the userform will open.

(At this point the Userform does not open. The message box appears saying " no more parcels are awaiting delivery "

Looking in the form drop down list, no names are seen.

 

[ipbr21054]

You might of started my list before saving and opening.

Type POSTED now.
Then save open etc and follow note again.

 

[Logit]

.
Saved ... closed workbook ... reopened and went to sheet POSTAGE.

"Do you want to open form ? " Clicked YES

Message box appears saying no more parcels await delivery.

????

 

[ipbr21054]

Red cells postage is capitals ?

 

[Logit]

.
Ok .. that was different.

Asked if want to open form. Clicked yes .. form opens and no names in drop down.


????

 

[ipbr21054]

So the code needs to accept red cells with text

 

[Logit]

.
Help me understand ... "So the code needs to accept red cells with text"

What do you want the workbook to do at this point ?