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

Senin, 04 Mei 2015

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

Paul,

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
            ActiveWorkbook.Worksheets(inx).Select
        Next inx

    Worksheets(SPYinx).Select

Application.ScreenUpdating = True
End Sub

Dave Gathmann

---In ExcelVBA@yahoogroups.com, <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
        Sheets(Sheets.Count).Select
        If (Sheets(inx).Visible = True) Then
            Sheets(inx).Select
            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?
 
Paul
-----------------------------------------
"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]" <ExcelVBA@yahoogroups.com>
 To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com> 
 Sent: Friday, April 24, 2015 10:14 AM
 Subject: [ExcelVBA] Re: Making a sowksheet the leftmost in the tabs
 

 
Donaldb36,

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.

Dave



  

__._,_.___

Posted by: dmgathmann@yahoo.com

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: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group