Hi guys,
I have a bit of a tricky situation going on. I want to create a workbook that takes away most of the manual work for a team that will use it, in some sense limits possible mistakes.
When they open the workbook a prompt should appear for them to select a specific field to edit in column C. Based on their decision it will change the header name of Column C. For example:
The list should contain (not limited to) "SOS" "Rounding" and "Purchase Group" If they choose SOS the header name for columns C will be EKGRP. If they choose Rounding the header name for columns C should be RDPRF etc. But if they have for some reason chosen the wrong field there should be a button "Change Field" that once pressed the same prompt can appear and they can choose the correct field to edit which will change the header name in Column C. This does not have to be in a table.
Different fields should save in different formats. for example. the three names mentioned above should save in a .CSV comma delimited. this is the current vba I have for this:
Dim fName As String
Dim fileSaveName As String
fName = "__" & " " & Format(Now(), "DD-MMM-YY") & ".csv"
If Right(fileSaveName, 4) = ".csv" Then
ActiveWorkbook.SaveAs Filename:=fileSaveName _
, FileFormat:=xlCSV, CreateBackup:=False
Else
MsgBox "You have not chosen a valid file name ending in .csv", vbOKOnly, "File Name Error!"
End If
But if the header name in column C is a specific value for Example "MMSTA" it should save in a text (tab delimited) .txt
The last thing this sheet should do. Once the button has been pressed "copy to CSV" a prompt should appear warning that the field they have chosen will be updated with a yes and no answer to choose. WHen they choose yes it continues to save in the correct format. If they choose no it has to take them back to the prompt to choose the correct field.
I have a bit of a tricky situation going on. I want to create a workbook that takes away most of the manual work for a team that will use it, in some sense limits possible mistakes.
When they open the workbook a prompt should appear for them to select a specific field to edit in column C. Based on their decision it will change the header name of Column C. For example:
The list should contain (not limited to) "SOS" "Rounding" and "Purchase Group" If they choose SOS the header name for columns C will be EKGRP. If they choose Rounding the header name for columns C should be RDPRF etc. But if they have for some reason chosen the wrong field there should be a button "Change Field" that once pressed the same prompt can appear and they can choose the correct field to edit which will change the header name in Column C. This does not have to be in a table.
Different fields should save in different formats. for example. the three names mentioned above should save in a .CSV comma delimited. this is the current vba I have for this:
Dim fName As String
Dim fileSaveName As String
fName = "__" & " " & Format(Now(), "DD-MMM-YY") & ".csv"
If Right(fileSaveName, 4) = ".csv" Then
ActiveWorkbook.SaveAs Filename:=fileSaveName _
, FileFormat:=xlCSV, CreateBackup:=False
Else
MsgBox "You have not chosen a valid file name ending in .csv", vbOKOnly, "File Name Error!"
End If
But if the header name in column C is a specific value for Example "MMSTA" it should save in a text (tab delimited) .txt
The last thing this sheet should do. Once the button has been pressed "copy to CSV" a prompt should appear warning that the field they have chosen will be updated with a yes and no answer to choose. WHen they choose yes it continues to save in the correct format. If they choose no it has to take them back to the prompt to choose the correct field.
Last edited: