Senin, 04 Mei 2015

Re: [ExcelVBA] Re: Making a sowksheet the leftmost in the tabs



Thanks for the code. I've been on vacation for the past week, and so I'm am tardy with my reply.

You are right in what I was looking for. From what I can tell, the visible property is whether a sheet is hidden vs just the tab for it being off the screen? I thought that I could use it to "scroll to the right" until the desired sheet was no longer visible, and then rescroll to the left to make it the leftmost when it became visible again. No luck, as the sheet was always visible  via Sheets.Visible. In any case, this morning I stumbled into scrolling to the last index and then just selecting the desired sheet. This puts is as far to the left as possible in the sheet tabs section.

Sub MakeLeft()
    Dim SPYinx As Integer, inx As Integer
Application.ScreenUpdating = False
    SPYinx = ActiveSheet.Index 'assume SPY active sheet
    For inx = SPYinx To ActiveWorkbook.Worksheets.Count
        Next inx

Application.ScreenUpdating = True
End Sub

Dave Gathmann

---In, <schreiner_paul@...> wrote :

I THINK what you're saying is that you DON'T want to change the order of the tabs.
if you have:
Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6, Sheet7, Sheet8, Sheet9, Sheet10 (and more)
you want them in the same order, but if Sheet6 is selected,
it would be in the leftmost position:
Sheet6, Sheet7, Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13
NOT reorder the sheets:
Sheet6, Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet7, Sheet8, Sheet9,
If this is the case, I wrote a quick loop
Sub Update_Sheet_Display()
    Dim inx
    For inx = 1 To Sheets.Count
        Application.ScreenUpdating = False
        If (Sheets(inx).Visible = True) Then
            Application.ScreenUpdating = True
            Debug.Assert False
        End If
    Next inx
End Sub
This will work until the last sheet is displayed on the right.
Is this what you're looking for?
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley

From: "David Gathmann dmgathmann@... [ExcelVBA]" <>
To: "" <>
Sent: Friday, April 24, 2015 10:14 AM
Subject: [ExcelVBA] Re: Making a sowksheet the leftmost in the tabs


Thanks for the reply, but that is not what I'm saying. My Worksheets(SPY) .Activate takes care of getting to the proper worksheet after the scrolling. What I'm saying is that there are, say, 40 worksheets and 15 - 20 can be displayed in the tabs at any one time depending on the characters in the worksheet (tab) name. The worksheet that I want to be active after the macro runs happens to have, say, the 10th index so it is the 10th tab from the left since for some reason the leftmost tab is set to index of one by going through the loop examining the worksheets.  I would like that 10th tab to be the leftmost tab shown. I hope that this clarifies the problem.



Posted by:
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Posting Komentar