Parse out commas?????

[jasgot]

I use this code, (thanks to this board ) to name and save my file based on user input. How can I yank out any commas that get put into the VB_Trigger, which will become the filename?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("VB_Trigger").Address Then
ThisFile = Range("VB_Trigger").Value
ActiveWorkbook.SaveAs Filename:="P:SYO Sales DocsQuotesSales" & ThisFile
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName
End If
End Sub


Thanks!

 

[Richie(UK)]

Hi jasgot,

Try:

<pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ThisFile As String

If Target.Address = Range("VB_Trigger").Address Then
ThisFile = Range("VB_Trigger").Value
ThisFile = Application.Substitute(ThisFile, ",", "")
ActiveWorkbook.SaveAs FileName:="P:SYO Sales DocsQuotesSales" & ThisFile
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName
End If

End Sub</pre>
HTH

PS If "Sales" is a sub-directory then you need another backslash at the end, before the quotes end.