VB in Excel (How To / Code Question) 1

[StarSearcher]

I have developed an Excel spreadsheet that will be used to track employee availability/time-off etc.. Naturally, I want to make it as "user friendly" as possible. I'm trying to set up a button that will select only columns C-AL in whatever row the user is on at the moment. If I try the "record a macro" option - it jumps back to the same row I recorded on. If I try manually coding, I can only get it to select an entire row. Help.

 

[BrainB]

r = ActiveCell.Row
ActiveSheet.Range("C" & r & ":AL" & r).Select


Regards
BrianB
** Let us know if you get something that works !
================================

 

[SkipVought]

StarSearcher,

BrianB's post is correct, but let me recommend yet another way to do the same thing that might more flexible when it comes to referencing columns...

With ActiveCell
  Range(Cells(.Row, "C"), Cells(.Row, "AL")).Select
End With

But I have a question for you...

Why are you selecting this range? What event will cause this range to be selected? Could you explain what you are trying to do?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[StarSearcher]

Thank you BrianB. Excellent solution. It works like a charm. Now I can stop banging my head on my desk. Thanks Again.

 

[StarSearcher]

Thanks Skip for your reply as well, I like it. I'll try to answer your question on what I'm doing.
Columns A&B list employee name and date in question, respectively. Columns C-AL represent the 8-5 workday broken into 15 minute increments. The sheet is filtered so an individual can select just their name, or a particular date. To block out an entire day for "PTO" the user would select columns C-AL, enter the letter "P" and press CTL+ENTER. As a result, a "P" would be placed in each cell and they would all be colored blue. We use P for PTO, T for Training, etc., with a seperat color for each code. This way, a manager can determine for instance, if all the members of of a project team will be available at a particular time.
Hope this makes sense. Thanks again.

 

[SkipVought]

KEWL!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Just a thought. You could set up three macros with hot-keys Alt-P, Alt-O, and Alt-T. With the idea that after the user selects a row he simply executes one of the three keystrokes.

Or if you want to be fancy, create a toolbar with three buttons to do the same thing.

I don't have the time, but if you are interested I'm sure Skip would be happy to show you how to do it.