"Mircrsoft Excel" their pop-up, how do I change that?

[Pinaceous]

Hi Everyone,

I have a protected sheet1 where the user pastes into this sheet1 a set number of cells.

When the user starts off in the wrong column and begins to paste their cells Microsoft Excel produces the following error message:

Mircrsoft Excel
The cell or chart you're trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab(you might need a password.)

So, How do I change this pop-up to my own creation.

For example:

MsgBox ("Please make sure that you are in Column B when pasting! Thank you!")

I've tried what I thought were logical approaches but am not achieving my message box.



Any suggestions???

 

[James006]

Hello,

Once you have determined the exact Target area ...just have a worksheet_change event macro to pop p your own userform ...

Hope thiswill help

 

[Pinaceous]

Hi James006,

Can you possibly provide an example?

Thanks!

 

[James006]

Hello again,

Below an event macro ... which needs to be stored in the worksheet module ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B2:D10")) Is Nothing Then MsgBox "Hello"
End Sub

Hope this will help

 

[Jaafar Tribak]

Unless you use James suggested workaround, this is no easy task. It requires installing a Windows hook to abort the built-in warning dialog ...I remember writing a small dll for this.

If you think It is worth going through the trouble of using a dll for this then I can post something here. BTW, the dll won't be in a seperate file but will be embedded inside the workbook and it won't need to be registered in ordered to be used either as it a standard windows dll NOT an activeX dll.

 

[James006]

@ Jaafar,

Would be extremely interested in your Windows hook ... :wink:

Cheers

 

[Jaafar Tribak]

@ Jaafar,

Would be extremely interested in your Windows hook ... :wink:

Cheers

Ok- which version of windows are you using ? 32Bit or 64bit ? the same for excel is it 32 or 64 bit ?

The version of the dll I wrote was 64bit .. I'll need some time to write the 32bit dll version.

 

[James006]

Ok- which version of windows are you using ? 32Bit or 64bit ? the same for excel is it 32 or 64 bit ?

The version of the dll I wrote was 64bit .. I'll need some time to write the 32bit dll version.

Thanks a lot for your answer ...

But .. You should not worry for me ... !!!

My question was really out of curiosity ... and since I am very old fashioned ... I am still using a 32Bit Windows XP ...

 

[Pinaceous]

Hi James006,

I really like your approach and appreciate it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B2:D10")) Is Nothing Then MsgBox "Hello"
End Sub

Thank you!
-Pin

 

[Pinaceous]

Hi Jaafar Tribak,

Can you Jaafar please provide for me an example code that you used installing how to abort the built-in warning dialog?

I'm using the 32bit version when your done writing your dll, I'd really like to try it out!

Thank you!
-Pin