[SOLVED] Simple VBA question

[Aaron]

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?

[JulieD]

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" <Aaron@discussions.microsoft.com> wrote in message
news:D910D007-2CA1-488D-A6CD-3AF714E189D0@microsoft.com...
>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?

[Chip Pearson]

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" <Aaron@discussions.microsoft.com> wrote in message
news:D910D007-2CA1-488D-A6CD-3AF714E189D0@microsoft.com...
>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?

[Tom Ogilvy]

Typing in a number in A1 (or any cell) triggers the change event. So you
can use the change event to pop up the message. The cell(s) triggering the
event are passed in the Target variable, - so first you check if Target
refers to A1, then, if so, evaluate the value and react.

Right click on the sheet tab of this sheet and select view code. In the
resulting module, at the top, in the left dropdown select worksheet. In the
right drop down select Change (not selectionchange).

You will get a procedure declaration like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

you can then put your code in that

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
If target.Address = "$A$1" then
if isnumeric(Range("A1").Value) Then
x = Range("A1").Value
If x = 1 Then
MsgBox "invalid number in cell A1"
End If
end if
End if
End Sub

See Chip Pearson's site on Events

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"Aaron" <Aaron@discussions.microsoft.com> wrote in message
news:D910D007-2CA1-488D-A6CD-3AF714E189D0@microsoft.com...
> 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?

[Dave D-C]

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?