Simple VBA question
 

I am teaching myself VBA. I have read and read tutorials and am trying to do
somthing very simple.

Public Sub firsttry()
Dim x As Integer
x = Range("A1").Value
If x = 1 Then
MsgBox "invalid number in cell A1"
End If
End Sub

I have to run the macro for the msgbox to appear. How can it pop up as soon
as the number 1 is entered in cell A1?

Simple VBA question
 

Hi Aaron

if you want it to run automatically when a value is entered in Sheet1 cell
A1 then you'll need to put the code in the Sheet1 module, not a standard
code module, as a worksheet_change event type code, e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = 1 Then
MsgBox "invalid number in cell A1"
End If
End If
End Sub

---
to use the code, right mouse click on the sheet tab of the sheet you want to
run it against and choose view code,
the vbe window will be displayed with the sheet you're working on
highlighted in the project explorer window, copy & paste the code directly
into there.

Cheers
JulieD


"Aaron" wrote in message
...
I am teaching myself VBA. I have read and read tutorials and am trying to
do
somthing very simple.

Public Sub firsttry()
Dim x As Integer
x = Range("A1").Value
If x = 1 Then
MsgBox "invalid number in cell A1"
End If
End Sub

I have to run the macro for the msgbox to appear. How can it pop up as
soon
as the number 1 is entered in cell A1?

Simple VBA question
 

You need to use event procedures, notably the Worksheet_Change
event procedure. Right click the worksheet tab and choose View
Code from the popup menu. In that code module, use code like

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Integer
X = Target.Value
If X = 1 Then
MsgBox "Invalid Number"
End If
End Sub

For more information about events, see
www.cpearson.com/excel/events.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Aaron" wrote in message
...
I am teaching myself VBA. I have read and read tutorials and am
trying to do
somthing very simple.

Public Sub firsttry()
Dim x As Integer
x = Range("A1").Value
If x = 1 Then
MsgBox "invalid number in cell A1"
End If
End Sub

I have to run the macro for the msgbox to appear. How can it
pop up as soon
as the number 1 is entered in cell A1?

Simple VBA question
 

You need to have your code as an event handler.
Go to the code for a sheet (right click a sheet tab).
Then
Private Sub Worksheet_Change( _
ByVal Target As Excel.Range)
Dim x As Integer
MsgBox target.address ' didactic
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
x = Range("A1").Value
If x = 1 Then
MsgBox "invalid number in cell A1"
End If
End Sub


Aaron wrote:
Public Sub firsttry()
Dim x As Integer
x = Range("A1").Value
If x = 1 Then
MsgBox "invalid number in cell A1"
End If
End Sub

I have to run the macro for the msgbox to appear. How can it pop up as soon
as the number 1 is entered in cell A1?