Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice for executing code
I am rather new to programming in Excel so I am hoping someone will help me
out here. I want to execute certain subroutines depending on which cell the user has just entered data into. Basically I want to check the user's input for validity. From what little I know of Excel programming I am thinking of doing something like this in the Worksheet_Change event: Private Sub Worksheet_Change(ByVal Target as Excel.Range) Dim t as Range Dim r as Range Dim i as integer t = Target r = Range("A:C") 'where columns A through C are the ones I want to check i = Intersect(t,r) Select Case i Case 1 'insert code for if user has left Column A Case 2 'Code for if user has left Column B Case 3 'Code for if user has left Column C Case Else exit sub End Select Does this make sense? Is there a better way to execute code when a user leaves a cell? As always, I thank everyone for there time and assistance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice for executing code
Unless you're coding in NET, you're missing some Set functions.
t = Target should be Set t = Target r = Range("A:C") should be Set r = Range("A:C") i = Intersect(t,r) should be Set i = Intersect(t,r) Next, you need to get the column number of i in your Select statement. Select Case i should be Select Case i.Column Beyond that, your code looks reasonably good. You'll want to disable events before your code makes any changes to the worksheet. Application.EnableEvents = False ' code Application.EnableEvents = True -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Steven Sutton" wrote in message ... I am rather new to programming in Excel so I am hoping someone will help me out here. I want to execute certain subroutines depending on which cell the user has just entered data into. Basically I want to check the user's input for validity. From what little I know of Excel programming I am thinking of doing something like this in the Worksheet_Change event: Private Sub Worksheet_Change(ByVal Target as Excel.Range) Dim t as Range Dim r as Range Dim i as integer t = Target r = Range("A:C") 'where columns A through C are the ones I want to check i = Intersect(t,r) Select Case i Case 1 'insert code for if user has left Column A Case 2 'Code for if user has left Column B Case 3 'Code for if user has left Column C Case Else exit sub End Select Does this make sense? Is there a better way to execute code when a user leaves a cell? As always, I thank everyone for there time and assistance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice for executing code
Target.Column gives you the column number of the first column of the range
the user modified data. Most times, user will edit one cell at a time, but be careful for those times when users may edit multiple cells at a time such as when pressing "Ctrl-Enter" (Enter the same data/formula into all selected cells), "Alt-Ctrl-Enter" (Enter an array formula into all selected cells), "Ctrl-D" (FillDown within selected cells, though from the row above, if only one row is selected), Ctrl-Insert (Paste into selected cells), and so on. For this reason, you may need to use Target.Columns.Count to know how many different columns were editted at one time with Target.Column being the left most column editted. There may even be one of those rare cases of multiple different ranges editted at a time, thus would need to use: Target.Areas.Count to see if just one range was used or if multiple ranges were used at the time of edited cells. Target.Areas(I) The above is a range object, which you can use for the same set of properties as on a single range. If you need to set a range to a range object, just as shown in the Sheet_Change event, you can use the following lines to declare, then initialize. Dim rngCurrent as Excel.Range, I as Long For I = 1 to Target.Areas.Count Step 1 Set rngCurrent = Target.Areas(I) <Perform your statements as needed Next I Set rngCurrent = Nothing As you can see, once an object variable is no longer in use, it's best to reset it back to nothing. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Steven Sutton" wrote in message ... I am rather new to programming in Excel so I am hoping someone will help me out here. I want to execute certain subroutines depending on which cell the user has just entered data into. Basically I want to check the user's input for validity. From what little I know of Excel programming I am thinking of doing something like this in the Worksheet_Change event: Private Sub Worksheet_Change(ByVal Target as Excel.Range) Dim t as Range Dim r as Range Dim i as integer t = Target r = Range("A:C") 'where columns A through C are the ones I want to check i = Intersect(t,r) Select Case i Case 1 'insert code for if user has left Column A Case 2 'Code for if user has left Column B Case 3 'Code for if user has left Column C Case Else exit sub End Select Does this make sense? Is there a better way to execute code when a user leaves a cell? As always, I thank everyone for there time and assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Continue executing code | Excel Programming | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming | |||
Code not executing | Excel Programming | |||
VBA code stops executing | Excel Programming | |||
Code Changes Not Executing | Excel Programming |