Pop Up with a list of items to choose from on opening new a new excel sheet

SteynBS

Board Regular
Joined
Jun 27, 2022
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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.

1675415958424.png


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:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.

View attachment 84505

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.
On second thought - a User Form might work better to change the column header in C3, but I still need help to save according to what info is in C3
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,373
Members
452,638
Latest member
Oluwabukunmi

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