Create Alert / Message in excel

G

gman1979

New Member
Joined
Oct 12, 2007
Messages
35
Hi,

I have created a number of data tracker in excel for my work, they are all linked in, due to some of the macro's contained within the workbooks I cannot protect them (such as hiding columns & rows). There are aprox 134 users that use these trackers on a daily basis. As a result there have been a few instances of formulas being typed over. What I am looking to do if on opening up, for an alert box to be displayed advising users not to overwrite any of the formulas as this can impact the real-time data they are trying to view. I had though of a semi-transparent text box, which upon clicking would hide, but I can’t figure out how to do this, and all the forums I have checked seems to only mention moving & resizing properties of the text box.

My second (and preferred) thought, would be a simple alert dialogue box to open every time the file is open which displays a message warning users not to type over any of the formulas. Any help with this would be greatly appreciated, even if it's something I haven’t thought of.

Thanks in advance

G
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome to the board. This will require some VBA code.

Right click the excel icon in top left corner by "file,edit,etc" - View Code

Paste the following

Code: 
Private Sub Workbook_Open()
MsgBox ("Do Not OverWrite Formulas")
End Sub
 
Upvote 0
Another alternative - to prevent overriding formula without worksheet protection, perhaps you can apply validation. Perhaps by setting the validation the same as the actual formula, that way only the result of the formula can apply.

Regards
Jon
 
Upvote 0
Nice Idea Jon...
Taking that a step further, you can run this macro to automatically put Each cell's formula as the data validation for that cell. So you don't have to manually do it for each formula....

Code: 
Sub advalidations()
For Each Cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        With Cell.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Cell.Formula
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Don't Overwrite My Formulas!!"
        .ShowInput = True
        .ShowError = True
    End With
Next Cell
End Sub
 
Upvote 0
Very good Jonmo1

Not wishing to overkill the suggested solutions, but I have suggested how you can still lock cells and not need to apply worksheet protection here.

This means that you can lock the cells and still prevent users from deleting the formula.

Using Jonmo1's similar method, you can group all formula and lock in one shot.

Select All (Ctrl+A) > Format > Cells > Protection > Uncheck "Locked"

then...

F5 > Special > Check Formulas

Format > Cells > Protection > Check 'Locked'

Hope this helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Hiding Sheets during the Before_Close Event
2
Replies
10
Views
401
Jaafar Tribak
Jaafar Tribak
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
207
still learning
S
S
  • Question
VBA Code - Message Box "If file exists"
Replies
9
Views
585
Spaztic
S
G
  • Question
Trouble pre-setting userform values based off user input
Replies
2
Views
134
gc2001
G
R
  • Question
Remove text box before saving using macros
Replies
1
Views
378
Micron
M

Forum statistics

Threads
1,216,028
Messages
6,128,399
Members
449,446
Latest member
CodeCybear

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top