Can you check my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Im going round in circles with a Compil Error End With Without With
Here is the code

Rich (BB 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.Visible = True And TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "Tracking Number Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox4.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"
    
ElseIf OptionButton7.Value = False And OptionButton8.Value = False And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
    Cancel = 1
    MsgBox "You Must Select An Postal Company", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton12.Value = False And OptionButton13.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT A USER NAME OPTION", vbCritical, "POSTAGE TRANSFER SHEET"
       
ElseIf OptionButton13.Value = True And TextBox9.Value = "" Then
    Cancel = 1
    MsgBox "YOU MUST ENTER A EBAY USER NAME", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox9.SetFocus
    
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
    .Cells(lastrow + 1, 2).Value = TextBox2.Text
    .Cells(lastrow + 1, 3).Value = TextBox3.Text
    .Cells(lastrow + 1, 5).Value = TextBox4.Text
    .Cells(lastrow + 1, 4).Value = TextBox6.Text
    .Cells(lastrow + 1, 9).Value = TextBox9.Text
    .Cells(lastrow + 1, 7).Value = "POSTED"
    .Cells(lastrow + 1, 4).NoteText Text:=TextBox10.Text
    
    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
    If OptionButton7.Value = True Then .Cells(lastrow + 1, 10).Value = "ROYAL MAIL": OptionButton7.Value = False
    If OptionButton8.Value = True Then .Cells(lastrow + 1, 10).Value = "DHL": OptionButton8.Value = False
    If OptionButton9.Value = True Then .Cells(lastrow + 1, 10).Value = "MY HERMES": OptionButton9.Value = False
    If OptionButton10.Value = True Then .Cells(lastrow + 1, 7).Value = "COLLECTION"
    If OptionButton10.Value = True Then .Cells(lastrow + 1, 10).Value = "COLLECTION": OptionButton10.Value = False
    If OptionButton11.Value = True Then .Cells(lastrow + 1, 10).Value = "N/A": OptionButton11.Value = False
    If OptionButton12.Value = True Then .Cells(lastrow + 1, 9).Value = "N/A": OptionButton12.Value = False
    If TextBox6.Value = "" Then .Cells(lastrow + 1, 4).Value = "NOTE"
    If TextBox6.Value = True Then
    
    With ActiveSheet.Cells(lastrow + 1, 4).Comment.Shape

        .Fill.ForeColor.RGB = RGB(255, 255, 255)
        .LINE.Weight = 1#
        
        With .TextFrame
            .AutoSize = True
            With .Characters
                With .Font
                    .Size = 12
                    .Name = "Calibri"
                    .Bold = True
                End With
            End With
        End With
    End With
    
Dim colorHTML As String, r As String, g As String, b As String
        If MsgBox("HAS THE SECURITY MARK BEEN APPLIED ?", vbYesNo + vbExclamation, "PINK SECURITY MARK MESSAGE") = vbYes Then
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        .Cells(lastrow + 1, 11).Value = "YES"
        Application.ScreenUpdating = True

        

End If
        MsgBox "CUSTOMER POSTAGE SHEET HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL UPDATE MESSAGE"
        Application.Goto Sheets("POSTAGE").Range("B" & Rows.Count).End(xlUp), True
        

err:
 Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
    If ActiveCell.Column = Columns("B").Column Then
 
    If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
        "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
        "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
        "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.") = vbYes Then
        
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbInformation, "HYPERLINK PHOTO MESSAGE"
        
        GoTo err
        End If
End If

End With

TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox1.Value = Now
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox6.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox2.SetFocus

NameForDateEntryBox.Clear
UserForm_Initialize


End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
Try moving the final End With to between these two lines
VBA Code:
   If TextBox6.Value = "" Then .Cells(lastrow + 1, 4).Value = "NOTE"
    If TextBox6.Value = True Then


That then shown Expected End With
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
Try this.
VBA Code:
Private Sub PostageSheetTransferButton_Click()
Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
Dim i As Long
Dim x As Long
Dim ctrl As Control
Dim lastrow As Long
Dim colorHTML As String, r As String, g As String, b As String

    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.Visible = True And TextBox4.Text = "" Then
        Cancel = 1
        MsgBox "Tracking Number Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
        TextBox4.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"
    ElseIf OptionButton7.Value = False And OptionButton8.Value = False And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
        Cancel = 1
        MsgBox "You Must Select An Postal Company", vbCritical, "POSTAGE TRANSFER SHEET"
    ElseIf OptionButton12.Value = False And OptionButton13.Value = False Then
        Cancel = 1
        MsgBox "YOU MUST SELECT A USER NAME OPTION", vbCritical, "POSTAGE TRANSFER SHEET"

    ElseIf OptionButton13.Value = True And TextBox9.Value = "" Then
        Cancel = 1
        MsgBox "YOU MUST ENTER A EBAY USER NAME", vbCritical, "POSTAGE TRANSFER SHEET"
        TextBox9.SetFocus
    End If

    If Cancel = 1 Then
        Exit Sub
    End If

    lastrow = ThisWorkbook.Worksheets("POSTAGE").Cells(Rows.Count, 1).End(xlUp).Row

    With ThisWorkbook.Worksheets("POSTAGE")
        .Cells(lastrow + 1, 1).Value = TextBox1.Text
        .Cells(lastrow + 1, 2).Value = TextBox2.Text
        .Cells(lastrow + 1, 3).Value = TextBox3.Text
        .Cells(lastrow + 1, 5).Value = TextBox4.Text
        .Cells(lastrow + 1, 4).Value = TextBox6.Text
        .Cells(lastrow + 1, 9).Value = TextBox9.Text
        .Cells(lastrow + 1, 7).Value = "POSTED"
        .Cells(lastrow + 1, 4).NoteText Text:=TextBox10.Text

        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
        If OptionButton7.Value = True Then .Cells(lastrow + 1, 10).Value = "ROYAL MAIL": OptionButton7.Value = False
        If OptionButton8.Value = True Then .Cells(lastrow + 1, 10).Value = "DHL": OptionButton8.Value = False
        If OptionButton9.Value = True Then .Cells(lastrow + 1, 10).Value = "MY HERMES": OptionButton9.Value = False
        If OptionButton10.Value = True Then .Cells(lastrow + 1, 7).Value = "COLLECTION"
        If OptionButton10.Value = True Then .Cells(lastrow + 1, 10).Value = "COLLECTION": OptionButton10.Value = False
        If OptionButton11.Value = True Then .Cells(lastrow + 1, 10).Value = "N/A": OptionButton11.Value = False
        If OptionButton12.Value = True Then .Cells(lastrow + 1, 9).Value = "N/A": OptionButton12.Value = False
        If TextBox6.Value = "" Then .Cells(lastrow + 1, 4).Value = "NOTE"
        If TextBox6.Value = True Then

            With ActiveSheet.Cells(lastrow + 1, 4).Comment.Shape

                .Fill.ForeColor.RGB = RGB(255, 255, 255)
                .Line.Weight = 1#

                With .TextFrame
                    .AutoSize = True
                    With .Characters
                        With .Font
                            .Size = 12
                            .Name = "Calibri"
                            .Bold = True
                        End With
                    End With
                End With
            End With


            If MsgBox("HAS THE SECURITY MARK BEEN APPLIED ?", vbYesNo + vbExclamation, "PINK SECURITY MARK MESSAGE") = vbYes Then
                TextBox1.Value = ""
                TextBox2.Value = ""
                TextBox3.Value = ""
                TextBox4.Value = ""
                TextBox6.Value = ""
                TextBox9.Value = ""
                .Cells(lastrow + 1, 11).Value = "YES"
                Application.ScreenUpdating = True
            End If

            MsgBox "CUSTOMER POSTAGE SHEET HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL UPDATE MESSAGE"
            Application.Goto Sheets("POSTAGE").Range("B" & Rows.Count).End(xlUp), True
        End If

err:


        If ActiveCell.Column = Columns("B").Column Then

            If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
                ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
                MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
            End If

        Else
            MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
            Exit Sub
        End If

        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
            If MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
                      "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
                      "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
                      "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.") = vbYes Then

                CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
                MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbInformation, "HYPERLINK PHOTO MESSAGE"

                GoTo err
            End If
        End If

    End With

    TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
    TextBox1.Value = Now
    TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox6.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox2.SetFocus

    NameForDateEntryBox.Clear
    UserForm_Initialize

End Sub


This shows me the Msg PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO

I have also noticed the bullect marks are now removed
I select ok 7 close down the userform & i then see the values have been transfered from userform to worksheet ???
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
If you place your code here and hit the indent button, you will be able to line up your if/end if and with/end with, statements


I dont see an indent option,EDIT i now see it but dont see how to use it so will stay here on the forum but thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
You are missing an End If line, but without knowing what is meant to happen & when it's impossible to know where it should go.
If you indented you code for every If, With etc it would be easier to detect what's wrong.
As a total guess, add another end If before the final End With.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

I tried what you advisedfluff but just gave another error.

This is what should happen

I add the customers name.
Add Description if needed
The bullet marks are ok
I add a username
I add the tracking number
I add a note in the textbox10

Now i press the command button shown as POSTAGE SHEET TRANSFER BUTTON on the form *******
I should see a msg asking has the securoity mark been added.
Select either yes or no
Another msg advising that the postage sheet has been updated.
Select ok
Another msg advising that there is no hyperlink for this customer

Obviously this issue at present stops when i press the command button marked as ******* above

When working the values are sent from userform to worksheet in the respective cells
 

Attachments

  • 424.jpg
    424.jpg
    142.8 KB · Views: 1

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
If this helps here is a working version BEFORE i made a change
The change i made was to have a textbox for where i would type some text.
This then when i sent values from userform to worksheet the comment would be placed in the cell at column D

It might be easier to use the working come and apply an edit so i can type some text on the userfrom & have it placed in the cell for that row in column D
This might be better / quicker 7 less stressfull for you

Hope that helps,now off to work

Thanks for your time with this


Rich (BB 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.Visible = True And TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "Tracking Number Not Entered", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox4.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"
    
ElseIf OptionButton7.Value = False And OptionButton8.Value = False And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
    Cancel = 1
    MsgBox "You Must Select An Postal Company", vbCritical, "POSTAGE TRANSFER SHEET"
    
ElseIf OptionButton12.Value = False And OptionButton13.Value = False Then
    Cancel = 1
    MsgBox "YOU MUST SELECT A USER NAME OPTION", vbCritical, "POSTAGE TRANSFER SHEET"
       
ElseIf OptionButton13.Value = True And TextBox9.Value = "" Then
    Cancel = 1
    MsgBox "YOU MUST ENTER A EBAY USER NAME", vbCritical, "POSTAGE TRANSFER SHEET"
    TextBox9.SetFocus
    
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
    .Cells(lastrow + 1, 2).Value = TextBox2.Text
    .Cells(lastrow + 1, 3).Value = TextBox3.Text
    .Cells(lastrow + 1, 5).Value = TextBox4.Text
    .Cells(lastrow + 1, 4).Value = TextBox6.Text
    .Cells(lastrow + 1, 9).Value = TextBox9.Text
    .Cells(lastrow + 1, 7).Value = "POSTED"
    .Cells(lastrow + 1, 4).NoteText Text:=TextBox10.Text
    
    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
    If OptionButton7.Value = True Then .Cells(lastrow + 1, 10).Value = "ROYAL MAIL": OptionButton7.Value = False
    If OptionButton8.Value = True Then .Cells(lastrow + 1, 10).Value = "DHL": OptionButton8.Value = False
    If OptionButton9.Value = True Then .Cells(lastrow + 1, 10).Value = "MY HERMES": OptionButton9.Value = False
    If OptionButton10.Value = True Then .Cells(lastrow + 1, 7).Value = "COLLECTION"
    If OptionButton10.Value = True Then .Cells(lastrow + 1, 10).Value = "COLLECTION": OptionButton10.Value = False
    If OptionButton11.Value = True Then .Cells(lastrow + 1, 10).Value = "N/A": OptionButton11.Value = False
    If OptionButton12.Value = True Then .Cells(lastrow + 1, 9).Value = "N/A": OptionButton12.Value = False
    If TextBox6.Value = "" Then .Cells(lastrow + 1, 4).Value = "NOTE"

    
Dim colorHTML As String, r As String, g As String, b As String
        If MsgBox("HAS THE SECURITY MARK BEEN APPLIED ?", vbYesNo + vbExclamation, "PINK SECURITY MARK MESSAGE") = vbYes Then
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        .Cells(lastrow + 1, 11).Value = "YES"
        Application.ScreenUpdating = True

        

End If
        MsgBox "CUSTOMER POSTAGE SHEET HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL UPDATE MESSAGE"
        Application.Goto Sheets("POSTAGE").Range("B" & Rows.Count).End(xlUp), True

err:
 Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
    If ActiveCell.Column = Columns("B").Column Then
 
    If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("THERE IS NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
        "WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
        "YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
        "NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.") = vbYes Then
        
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbInformation, "HYPERLINK PHOTO MESSAGE"
        
        GoTo err
        End If
End If

End With
    
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox1.Value = Now
TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox6.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox2.SetFocus

NameForDateEntryBox.Clear
UserForm_Initialize
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is this what you mean
VBA Code:
      If TextBox6.Value = "" Then
         .Cells(lastrow + 1, 4).Value = "NOTE"
      Else
         .Cells(lastrow + 1, 4).Value = TextBox6.Text
      End If
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
Is this what you mean
VBA Code:
      If TextBox6.Value = "" Then
         .Cells(lastrow + 1, 4).Value = "NOTE"
      Else
         .Cells(lastrow + 1, 4).Value = TextBox6.Text
      End If
Yea that was part of it.
That would place the word NOTE in cell D to show the user there is a comment as without the word NOTE some users don’t see that little red marker in the cell.
The

You will see in my code that if TextBox6 has a value then NOTE isn’t added and also TextBox10 is hidden on the user form.
Reason being if I type a value in TextBox6 then I won’t be typing in TextBox10.

My code for the working / styling on the comment box is as follows.
On a phone and unable to add the code I will take a screen shot and add in next post.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,698
Office Version
  1. 2007
Platform
  1. Windows
See this

VBA Code:
With ActiveSheet.Cells(lastrow + 1, 4).Comment.Shape

        .Fill.ForeColor.RGB = RGB(255, 255, 255)
        .LINE.Weight = 1#
        
        With .TextFrame
            .AutoSize = True
            With .Characters
                With .Font
                    .Size = 12
                    .Name = "Calibri"
                    .Bold = True
                End With
            End With
        End With
    End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
I'm afraid I have no idea what your saying. Does that amendment I suggested solve the problem you had?
 

Watch MrExcel Video

Forum statistics

Threads
1,112,796
Messages
5,542,549
Members
410,560
Latest member
1ndependent
Top