Need message box code to change background color in macro

**BGiffin2004** · 11-26-2011, 05:51 AM

I have a macro creating background color. I created a message box that allows the user to change the color originally prescribed in the macro. I just do not know what code to insert after the message box so the user input changes the color of the macro.

Any references gladly accepted. I have searched extensively.

See attached workbook. - I am using Excel 2010 with Windows XP (32bit).

**BGiffin2004** · 11-30-2011, 02:15 AM

Bump no response

I greatly simplified this post!

**mikerickson** · 11-30-2011, 02:44 AM

Have you considered this way to control cell's color:

Please Login or Register  to view this content.

To get the return value from an Input Box, you have to assign it to a variable

Please Login or Register  to view this content.

**BGiffin2004** · 11-30-2011, 05:08 AM

I do not know all the code in your first description to control a cell’s color. I am going to research the applicable significance of “Application.Dialogs”, & “Cells (1,1)” for a better understanding now. Thanks for caring enough to send this additional learning curve.

I understand the code in the 2nd code window. Thanks much.

It may take me a few hours yet to absorb all this bit that you have included above..., and marked it “Solved”, or not. I am going to eat my mashed potatoes that just came out of the microwave and internet search all the details above now. Have a good evening mikerickson!

**BGiffin2004** · 11-30-2011, 08:16 AM

I used mikerickson code suggestion (2nd listed code suggestion in the original reply post). It worked fine…, until I tried to account for a null value if the user just hit the OK button - without entering any number. When I entered this code I get a Runtime Error 13 “Type mismatch”.

When I look up Error 13 on the internet it sounds as if my I need to download Microsoft’s Service Pack 3. Does anyone have experience with this? – Perhaps it is something wrong with my coding after all, and there is no need for trying to locate MS Service Pack3? In other words, is there a handy work-around? Thanks.

This is the line Excel finds a bug with:

Please Login or Register  to view this content.

I have attached a copy of the work book and Word Doc of the error code. I also copied and pasted the relevant code below.

Please Login or Register  to view this content.

**mikerickson** · 11-30-2011, 02:19 PM

This would protect against user entering something silly (like "cat")

Please Login or Register  to view this content.

**BGiffin2004** · 11-30-2011, 09:19 PM

Thanks again mikerickson.
The code for handling unnacceptable entries works perfectly.
I am going to mark this post most definitely "SOLVED".

* I am still working on this program to SAVE the entry using "GetSaveAsFilename". The maco wants to save it as an "xlsmword" document. - But I will post this as a separate issue/post on the Excel Forum website if I am having difficulty figuring it out.

** (In case you were still curious though - I attached the workbook with all the GetSaveAsFilename new code in Module1/macro "SaveChanges", and a Word doc showing the warning messages and highlighted code for what I think the problems are with the code..., and therefore what I will be working on.) Again, I will post this in the Excel forum under a new post heading if I am not able to format the workbook for saving it in "xlsm" format.

I could not attach ColorChanges(2)Test(2).xlsmword (?); so I am attaching ColorChanges(2).xlsm

**BGiffin2004** · 12-01-2011, 03:32 AM

mikerickson,
Just wanted to let you know I got everything done and produced a beautiful program/workbook thanks to your help. It is a lot more complex than the "background color change exercise" that you originally helped me out with. (And I will not need to post for anymore help!) - I am attaching a copy for your entertainment. (Click on the black buttons at the top of the screen.) Thanks so much!