Highlight Active Worksheet Tab in VBA/macro

[greycrux]

Hi,

I've created a macro to copy a worksheet to the first position in a workbook and clear the user contents cells. Simple enough. Before I exit, I'd like to highlight the new worksheet's name tab, so the user is prepped to give the worksheet a descriptive name. The macro record feature isn't picking this up, so I'm hoping there's a way to do it in VBA. I'm using Excel 2002 SP3.

Here's my code

Sub NewReport()
'
' NewReport Macro
' Macro recorded 8/29/2006 by Lloyd Cross
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Sheets(1).Copy Before:=Sheets(1)
    Range("B2:B11").Select
    Selection.ClearContents
    [b]<<Highlight Active Sheet's Name Tab>>[/b]
End Sub

Thank you.

 

[xlbo]

AFAIK, you cannot do this - a worksheet doesn't have a method associated with the name property so I don't think you can leave it highlighted or in fact interact with the tab name in any way other than to change it.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

An alternative solution is to extend the code: ask for new name in an input box, test for proper name (length, characters, other sheets) and rename worksheet.

combo

 

[misterstick]

or, better yet, instead of trying to second guess excel's naming conventions (they do, occasionally, change) try to set the name property on the sheet with your new string, and trap any errors, reprompting if necessary.



mr s. <

 

[greycrux]

Thank you all for the answers. Looks like prompting with an input box, using code to check that the user input conforms to the conventions my firm requires, error trapping to ensure the new name conforms to excel's conventions, and re-prompting if necessary is the way to go. I'll get working on it!

Thanks again!