Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple IF question | Excel Discussion (Misc queries) | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
simple question, hopefully a simple answer! | Excel Programming | |||
a simple question | Excel Programming |