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 / Setup / January 2006

Tip: Looking for answers? Try searching our database.

Automate column selection

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Snoopy - 27 Jan 2006 03:38 GMT
Is it possible to set up excel so that the proper column is selected for
numerical entry dependent upon a text entry in a row?
Dave Peterson - 27 Jan 2006 15:26 GMT
Maybe.

Should a cell be selected after you type that text value in a certain column?

Or should a cell be selected when you select a cell in that row -- and the text
value in that other cell already exists???

I'm gonna guess that you type the value in column A.  Then you select a cell in
the same row.

If you want to try:

Right click on the worksheet tab that should have this behavior.  Select view
code and paste this into the code window that opens up.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim myOffset As Long
   
   If Target.Cells.Count > 1 Then Exit Sub
   If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
   
   On Error GoTo errHandler:
   
   myOffset = 0
   Select Case LCase(Target.Value)
       Case Is = "a": myOffset = 1
       Case Is = "b": myOffset = 3
       Case Is = "x": myOffset = 18
   End Select
   
   If myOffset <> 0 Then
       Application.EnableEvents = False
       Target.Offset(0, myOffset).Select
   End If
   
errHandler:
   Application.EnableEvents = True

End Sub

You'll have to change the values and offsets (18 means move to the right 18
columns).  And the values that get typed into column A, too.

Then back to excel to test it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

> Is it possible to set up excel so that the proper column is selected for
> numerical entry dependent upon a text entry in a row?

Signature

Dave Peterson


Rate this thread:






 
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.