VBA Excel: replace values based on a config sheet with msgbox.

Hello,

I have the following procedure which allows me to replace values based on config sheet.
I would like to add the procedure to my personal.xlsb with the following requirements:

1-Add msgbox (yes, no) which says “Have you already created config sheet?” vbCtrl
“Sheet Name: Config-Replacement” vbCtrl
“Column A values to replace” vbCtrl
“Column B revised values” vbCtrl
“Column C reference column in which are located values to replace” vbCtrl
“Column D sheet name in which replacement procedure should be applied" vbCtrl
“Column E flag value: 1 for exact values” vbCtrl

2-If vbno is activated the config sheet reported in my example should be created with format and
headers. Else Run the replacement procedure
3-If vbno is activated and config sheet already exist. Exit sub and display msgbox which says “Config sheet already exist."


If you have questions, please contact me.
Replace-values-based-on-config-shee.xlsm
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You don't really need to ask if the sheet is created, simply check for it's existence. If it doesn't exist create it.
0
Roy CoxGroup Finance ManagerCommented:
0
Roy CoxGroup Finance ManagerCommented:
This might be what you want

Sub CreateIT()

    Select Case MsgBox("Do you need to create the Config Sheet?", vbYesNo, "Config Required")
    Case vbYes:
        ActiveWorkbook.Sheets.Add
        With ActiveSheet
            .Name = "Config-Replacement"
            .Range("A1:G1").Value = Array("Original Value", "Revised Value", "Col letter", "Dest Sheet", "Like=0 OK, Exact=1, Blank=2", "V Replaced in Dest", "Already Launch")
            .Range("A1:G1").WrapText = True
            .Range("A1:G1").ColumnWidth = 17.7
            .Range("A1:G1").RowHeight = 30.75
            .Range("A1:F1").Interior.ColorIndex = 4
            .Range("G1").Interior.ColorIndex = 6
        End With
        
        MsgBox "Config sheet created", vbInformation, "Success"
    Case Else
        Replacement2
    End Select

End Sub

Open in new window


Note ActiveWorkBook and ActiveSheet used to allow the code to be stored in PERSONAL.xlsb
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LD16Author Commented:
Roy, thank you for this proposal. I will test it as soon as I can.
0
Roy CoxGroup Finance ManagerCommented:
I'm not sure if I have the part correct if No is selected. I thought you wanted to run your macro.
0
Roy CoxGroup Finance ManagerCommented:
I've re-written the code to check if the sheet already exists before using the message box. If it doesn't exist the prompt will appear, I've left the option to cancel by selecting No
Replace-values-based-on-config-shee.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Thank you for this proposal. I tested and it works!
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help, don't forget to close the question by assigning the points
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.