Data being cleared in cells when OK pressed on msgbox

**SChapman** · 05-13-2013, 10:50 AM

Hi,

I have the code below for a msgbox to prompt the user when particular cells have no value, this is within a Macro to copy data to a master sheet. For some reason when I click OK on the msgbox the cells are then emptied.

Please Login or Register  to view this content.

Can anyone explain how I can prevent this?

Thanks

**AndyLitch** · 05-13-2013, 10:56 AM

Try replacing the above with this

Please Login or Register  to view this content.

It may/may not work

**SChapman** · 05-13-2013, 11:18 AM

Unfortunately not, I get a type mismatch

This code is within a macro to copy data over to a master sheet, I need the msgbox to run if certain cells have no value & if cells have no value then exit sub at that point & don't copy the data over.

**AndyLitch** · 05-13-2013, 11:31 AM

I must admit, I'm not too familiar with the syntax used there.. I'm assuming that ws1.Range("FinancialControlsData", "CompetenceData") are two named ranges ??
If so, you could go to Formulas--> Name manager......... Select the named range and select the formula bar at the bottom - Excel will highlight what the range applies to - there may be a problem with the range itself..

**SChapman** · 05-13-2013, 11:35 AM

Yes Andy, they are named ranges. The code I posted first worked fine apart from the fact it was emptying data in all the cells specified when OK was pressed in the msgbox.

Without the names it would be as follows.....

Please Login or Register  to view this content.

I did try with & without named ranges but with no joy

**JosephP** · 05-13-2013, 11:43 AM

perhaps

Please Login or Register  to view this content.

**SChapman** · 05-13-2013, 11:47 AM

Thanks for the response Joseph, this also works the same as my initial code however it is once again emptying the contents of the other cells :/

**AndyLitch** · 05-13-2013, 11:50 AM

If it's showing you the messagebox and then emptying the ranges anyway there must be some code elsewhere that's doing the damage because the exit sub will take you straight out....

**JosephP** · 05-13-2013, 11:52 AM

the code I posted doesn't do anything to any cells-did you leave your

Please Login or Register  to view this content.

line in? that's the one clearing the cells

**SChapman** · 05-13-2013, 11:53 AM

No removed all of my code & replaced with yours when tested.

**JosephP** · 05-13-2013, 11:57 AM

then you must have some other code running

**SChapman** · 05-13-2013, 11:59 AM

I do, I'm leaving the office now so will post more detail when I get home.

Thanks for your help so far guys

**SChapman** · 05-14-2013, 02:14 AM

Joseph, I've tried this again at home & strangely it appears to be working. How can I get it to highlight the empty cells Yellow?

Thanks again for your help guys

**JosephP** · 05-14-2013, 04:14 AM

perhaps

Please Login or Register  to view this content.

**SChapman** · 05-14-2013, 04:57 AM

Thanks again Joseph, is it possible to remove the highlight once data has been entered?

**JosephP** · 05-14-2013, 05:00 AM

sure thing

Please Login or Register  to view this content.

**SChapman** · 05-14-2013, 05:08 AM

I tried CF but unfortunately it appears to prioritise over VBA

**JosephP** · 05-14-2013, 05:09 AM

if you use cf you wouldn't need the vba to do any coloring :-)

**SChapman** · 05-14-2013, 05:12 AM

That doesn't appear to work, the cell stays highlighted yellow once data is entered :/

**JosephP** · 05-14-2013, 05:26 AM

the code or the cf?

a sample workbook could speed this up ;-)

**SChapman** · 05-14-2013, 07:15 AM

The code, I've given up on the CF due to using code like you said.

**JosephP** · 05-14-2013, 08:20 AM

what's in the cells-literal values or formulas? the code should work for literal values

**SChapman** · 05-14-2013, 10:04 AM

Values, no formulas. If you need a sample then I'll post one later

**JosephP** · 05-14-2013, 10:31 AM

yes please

**SChapman** · 06-08-2013, 06:44 AM

Hi Joseph,

It has been a while since I have managed to continue the work on this hence the long delay. I now have some time to carry on. I need to edit your code to check if there is data in a particular column of the table & if there isn't I need it to ignore the empty cells. I have edited it as follows but it doesn't appear to work. Can you help me with this?

Please Login or Register  to view this content.

Thanks

Data being cleared in cells when OK pressed on msgbox

LinkBack

Thread Tools

Rate This Thread

Display

Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Re: Data being cleared in cells when OK pressed on msgbox

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions