message box/input box

npiere

New Member
Joined
Sep 4, 2002
Messages
2
I have a simple worksheet where E29 has a formula that the result cannot exceed B23. I want to write a macro that will automatically pop up a message box that says "Cropland exceeds bases..." and then maybe an input box where they can change their numbers for cells E23:E27... can this be done or am I making this way too complicated?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This will give your conditional MsgBox, I will try to add the InPutBox next. JSW

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If [E29] > [B23] Then
MsgBox "Cropland exceeds bases..."
End If
End Sub

P.S. This code go's in the Sheet Module, Right click the sheet tab that your data is on, select "View Code" then paste the above code in, it works automatically when ever the value go's over the base amount. JSW


_________________<INPUT type="JSW" value=" Excel On... Coder's!" ID=Text1><spanstyle='font-size:36.0pt;color:#FFCC00'>JSW<o:p></o:p></span>[/b]</p><spanstyle='font-size:36.0pt;color:#FFCC00'>Try and try again, " The way of the coder!"<o:p></o:p></span>[/b]
This message was edited by Joe Was on 2002-09-05 12:02
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
You could use the Worksheet_Calculate event which will run a macro whenever there is a calculation on the worksheet. There is also Worksheet_Change event, not sure which is best for your needs, but you can read about it in the help section. There is also probably a way to check for a change/calculation in a given cell, instead of the whole sheet.

Once you determine which above works best, you can do something like this as the code:

If e29>b23 then E29=InputBox("Please Select a New Value for Cell E29")

If you need more explicit help with the code, post back, but this should at least get you in the right direction.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This will also ask if you want to update the base amount, if so it will make the change. JSW

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim myNum As Integer

If [E29] > [B23] Then
MsgBox "Cropland exceeds bases..."
myNum = Application.InputBox("If you would like to change the base amount:" & Chr(13) & _
Chr(13) & "Enter a new amount:")

[B23].Value = myNum
Else: End
End If
End Sub
 

bkpchs237

New Member
Joined
Sep 4, 2002
Messages
3

ADVERTISEMENT

Regarding the first part, is it really necessary for you to write code for the message box? Using the data validation feature for that one cell would accomplish this task.

Hope this helps.
 

bkpchs237

New Member
Joined
Sep 4, 2002
Messages
3
Regarding the first part, is it really necessary for you to write code for the message box? Using the data validation feature for that one cell would accomplish this task.

Hope this helps.
 

bkpchs237

New Member
Joined
Sep 4, 2002
Messages
3
Regarding the first part, is it really necessary for you to write code for the message box? Using the data validation feature for that one cell would accomplish this task.

Hope this helps.
 

Forum statistics

Threads
1,144,363
Messages
5,723,914
Members
422,527
Latest member
JayTheKaz

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
Top