Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Best practice for executing code

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steven Sutton - 17 Mar 2008 22:45 GMT
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.
Chip Pearson - 17 Mar 2008 23:00 GMT
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

Signature

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
   Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

>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
[quoted text clipped - 31 lines]
>
> As always, I thank everyone for there time and assistance.
Ronald R. Dodge, Jr. - 17 Mar 2008 23:07 GMT
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.

Signature

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

>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
[quoted text clipped - 31 lines]
>
> As always, I thank everyone for there time and assistance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.