Sheet Tabs: API manipulation to a multiline TabControl

[Guest]

Has anyone ever managed to get a window handle for the sheet tabs control and
run SendMessage TCS_MULTILINE?

SPY++ is surprisingly unhelpful: the EXCEL7 window for the active workbook
has the expected scrollbars, and three XLCTL windows which I am unable to
identify. None of them respond to SendMessage(hwndCtrl, TCS_MULTILINE, 0&,
0&) - actually, I'd be interested to know what these controls are...

As for the reason I'm doing this, I have some workbooks here with 20 to 30
tabs, and the users want faster navigation. Yes, I could populate a menubar
dynamically with navigation buttons for each worksheet in the current
workbook, and dock it to the bottom of the XLDESK window. But I like lazy
solutions.

 

[Jim Rech]

I doubt the sheet tabs are windows. Anyway, do your users know about the
built-in sheet list feature (right-click on one of the four little arrow
heads in the lower left corner of the worksheet window)?

--
Jim
| Has anyone ever managed to get a window handle for the sheet tabs control
and
| run SendMessage TCS_MULTILINE?
|
| SPY++ is surprisingly unhelpful: the EXCEL7 window for the active workbook
| has the expected scrollbars, and three XLCTL windows which I am unable to
| identify. None of them respond to SendMessage(hwndCtrl, TCS_MULTILINE, 0&,
| 0&) - actually, I'd be interested to know what these controls are...
|
| As for the reason I'm doing this, I have some workbooks here with 20 to 30
| tabs, and the users want faster navigation. Yes, I could populate a
menubar
| dynamically with navigation buttons for each worksheet in the current
| workbook, and dock it to the bottom of the XLDESK window. But I like lazy
| solutions.

 

[Timo Kunze]

SendMessage(hwndCtrl, TCS_MULTILINE, 0&, 0&)

TCS_MULTILINE is a control style and no message. If you want to check
whether it is set, you would have to use something like this:

Dim style As Long

style = GetWindowLong(hwndCtrl, GWL_STYLE)
If style And TCS_MULTILINE Then
Debug.Print "Control uses TCS_MULTILINE!"
End If

And if you want to set this style, do it like this:

Dim style As Long

style = GetWindowLong(hwndCtrl, GWL_STYLE)
style = style Or TCS_MULTILINE
SetWindowLong hwndCtrl, GWL_STYLE, style

Timo

 

[Guest]

Thanks for clarifying that: yes, I should be using SetWindowLong.

Unfortunately, the three XLCTL windows turn out to be resizing controls for
the workbook scrollbars; the TabStrip control is well and truly hidden.
Visible Excel objects are an odd mix of standard Windows components for
exotic jobs and exotic handcrafted objects for commonplace Windows actions.

Oh well: looks like I'll have to do some work... For people who value their
time so much that a worksheet tab that's scrolled out of view is uneconomical
to chase with the mouse.

Nile

________________________________________________________________
"The Meek Shall Inherit The Earth. In very small plots, about six feet by
three"

 

[Guest]

Ooh *SHINY!*

I didn't know about that menu. And if there's more than 16 sheets, clicking
the 'More Sheets...' menu option opens the 'Activate' dialogue, which I can
slip into the toolbar with this line of code:

Application.Dialogs(xlDialogActivate).Show

Thanks for that.

NH