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

Luis Diaz
Luis Diaz used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
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.
Roy CoxGroup Finance Manager

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Luis DiazIT consultant

Author

Commented:
Roy, thank you for this proposal. I will test it as soon as I can.
Roy CoxGroup Finance Manager

Commented:
I'm not sure if I have the part correct if No is selected. I thought you wanted to run your macro.
Group Finance Manager
Commented:
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
Luis DiazIT consultant

Author

Commented:
Thank you for this proposal. I tested and it works!
Roy CoxGroup Finance Manager

Commented:
Pleased to help, don't forget to close the question by assigning the points

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial