Can you check my code please

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,829
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
 
Sort by date Sort by votes
Fluff said:
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
Click to expand...


That then shown Expected End With
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Norie said:
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
Click to expand...


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 ???
 
Upvote 0
davesexcel said:
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

www.automateexcel.com

VBA Online Code Indenter

Copy and paste your VBA code into the online editor. Press Indent and your VBA code will be cleaned and formatted.
www.automateexcel.com
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
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: 11
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
I'm afraid I have no idea what your saying. Does that amendment I suggested solve the problem you had?
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
I
  • Question Question
Please explain this line of code
Replies
1
Views
171
RoryA
RoryA
I
  • Question Question
Vba has stopped adding hyperlink to my customers name
Replies
3
Views
101
ipbr21054
I
I
  • Question Question
Find customer in column & link to his pdf
2
Replies
16
Views
638
NoSparks
N
I
  • Question Question
Added new code but keep seeing Block If Without End If Mesage
Replies
3
Views
146
RoryA
RoryA

Forum statistics

Threads
1,224,567
Messages
6,179,571
Members
452,927
Latest member
whitfieldcraig

Share this page

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
Back
Top