If Else advice please if cell has a specific value

[ipbr21054]

I am using the code shown below but i would like to add an If Else line of code but not sure on how to do it.

The code below will be used the same both times but with or without the additional value added when saved.

Before the file is saved / printed etc i would like the code to check for a value in a cell.
The cell is question is N1
There will be various values in the cell but will only ever consist of 1 letter
The letter im interested in is M

So what i need to add to my existing code If cell N1 ="M" then save the file the same as the line of code shown in blue but with the additional value of *SUS* to the saved output
Else just save it using the blue line of code.

The output will be like my example below depending if cell N1 = M or not

TOM JONES
16-10-2023
ABC 123

or

TOM JONES
16-10-2023
ABC 123
*SUS*

Private Sub PurchasedKey_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
 
  If TextBox1 = "" Then
  MsgBox "YOU DID NOT ENTER A CUSTOMERS NAME", vbCritical, "NO NAME ENTERED ON SHEET"
  TextBox1.SetFocus
  Exit Sub
   
  End If
 
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text
    .Range("E3") = Format(DateSerial(CLng(Me.cboYear.Value), Me.cboMonth.ListIndex + 1, Me.cboDay.Value), "long date")
   
  End With
  Unload PrinterForm
                                                                            
  With ActiveSheet
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
ENTER LINE OF CODE HERE PLEASE
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
       

    With ActiveSheet
    ActiveWindow.SelectedSheets.PrintOut copies:=1

 End With
 End With
End Sub

I tried this but my code was incorrect

    If .Range("N1") = M Then
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & " " & *SUS* ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    Else
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If

    With ActiveSheet
    ActiveWindow.SelectedSheets.PrintOut copies:=1

 

[6StringJazzer]

You just need quotes:

    If .Range("N1") = "M" Then

VBA thinks that M is a variable. You have not declared it, so Excel thinks it is a Variant with a value of 0. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors. If you had done this, you would have gotten a compiler error pointing out the problem.

 

[ipbr21054]

So now using the above that makes my code like so but this line is now shown in Red

    If .Range ("N1") = "M" Then
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & " " & *SUS* ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    Else
     
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  
    MsgBox "PDF COPY HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
 End With
End Sub

 

[ipbr21054]

The code blow is what is in place so i will now leave for any help it as im getting nowhere.
By changing the value shown below in Red im getting syntax error, value not defind etc hence why i will now leave it & over to some help.


@MARK858 this is right up your street i think.
Thanks

    If .Range("N1") = "M" Then
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & " " & (*SUS*).Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    Else
      
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & " " & .Range("AB1").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If
    MsgBox "PDF COPY HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
 End With
End Sub

 

[6StringJazzer]

What is *SUS* supposed to be? "*" is not a valid character for a filename, and the way you are using the string is not valid for a wildcard. The two ways you have tried are both invalid syntax

& *SUS* ".pdf"

& (*SUS*).Value & ".pdf"

But it's not possible to tell what you intend by looking at what doesn't work.

 

[ipbr21054]

If M is present is the cell I would like the saved output file to also include *SUS* if * isn’t allowed then just SUS

 

[ipbr21054]

The cell in question is N1
The value of inter is the letter M

So depending on whether the value M is in cell N1 depends on how i would like the file saved.

If cell N1 value is M then save like this,

TOM JONES
16-10-2023
ABC 123
SUS.pdf

If cell N1 has any other value then save like this,

TOM JONES
16-10-2023
ABC 123.pfd

I know the syntax is wrong so i am just i looking at various ways to try & correct it but was so far im unable to.
So i await help from the group

Thanks

 

[6StringJazzer]

Change the code you showed as red from this

" " & (*SUS*).Value & ".pdf"

to this

" SUS.pdf"

 

[ipbr21054]

Thanks all done.

I was following the method like the other but yours worked

Thanks