message box/input box

[npiere]

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?

 

[Joe Was]

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></o></span>[/b]</p><spanstyle='font-size:36.0pt;color:#FFCC00'>Try and try again, " The way of the coder!"<o></o></span>[/b]
This message was edited by Joe Was on 2002-09-05 12:02

 

[thereuare]

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]

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]

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]

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]

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.