Using a Userform as a multiple worksheet navigation bar

[Frizzlediam]

The excel document that I am working on has several worksheets that show and hold different data. When others review this information I would like to have a consistent navigation bar that stays present for quick and controlled page changes.

I have searching the net for days and the most promising solution is a UserForm. The reason I am doing this rather than hyperlinking into a shape is because a couple of those pages have to be froze with with data labels across the left and the top edge. When scrolling, the navigation bar is also scrolled away and I do not like this.

I have created a UserForm with the buttons that I need, I have it set to modeless so that I can continue to work in my document. I even found a way to strip the frame and header away. The one thing I cannot seem to accomplish is figuring out how to define the position of the UserForm based upon the location of the excel workbook and then keep it in that position regardless of minimizing, maximizing, moving the window etc.

I have been trying to figure out how to make Chip Pearson's code from his Userforms And The SetParent Function page on his website work for me because it looks like making a UserForm the child of the application might be exactly what I need to do. Unfortunately the code provided does not work for me as provided (I am using office 2013 in a corporate setting) and I have not been able to find out why. I have googled and googled to no avail. I downloaded his sample document and it works, beautifully in the sample. I however am not smart enough to figure out why it works there and not with the provided code. The example code is just way too complicated for me.

I have taken all of Chips code out of my document to allow for anyone with any other ideas of how to achieve the same thing to offer their ideas. This is what I have so far.

I would sincerely appreciate any help I could get this method to work or any other ideas to achieve the same thing. I apologize in advance if I have missed something but I have been to so many pages I can't even remember what I have looked at and what I haven't anymore. I swear if I had hair before starting this venture I would have pulled it all out!

I would like to preemptively apologize if I made any mistakes posting this as it is my first.

Eventually I will set this to initiate upon opening the excel document but for now I have it on a button


Sheet8(Group AVerages (STATS)) <-Worksheet Name

Private Sub CurrentTechList_Click()UserForm1.Show vbModeless
End Sub

UserForm1

Private Sub CurrentTechList_Click()
Sheets("Current Tech List").Select
ActiveSheet.Cells(1, 1).Select


End Sub
Private Sub RunningTotal_Click()
Sheets("Running Total").Select
ActiveSheet.Cells(1, 1).Select


End Sub
Private Sub GroupAveragesSTATS_Click()
Sheets("Group Averages (STATS)").Select
ActiveSheet.Cells(1, 1).Select


End Sub
Private Sub GroupAverages_Click()
Sheets("Group Averages").Select
ActiveSheet.Cells(1, 1).Select


End Sub
Private Sub ThisMonthsScores_Click()
Sheets("This Months Scores").Select
ActiveSheet.Cells(1, 1).Select


End Sub
Private Sub EvaluationNotes_Click()
Sheets("Evaluation Notes").Select
ActiveSheet.Cells(1, 1).Select


End Sub

Module1

Sub RemoveTitleBar(frm As Object)
    Dim lStyle          As Long
    Dim hMenu           As Long
    Dim mhWndForm       As Long
     
    If Val(Application.Version) < 9 Then
        mhWndForm = FindWindow("ThunderXFrame", frm.Caption) 'for Office 97 version
    Else
        mhWndForm = FindWindow("ThunderDFrame", frm.Caption) 'for office 2000 or above
    End If
    lStyle = GetWindowLong(mhWndForm, -16)
    lStyle = lStyle And Not &HC00000
    SetWindowLong mhWndForm, -16, lStyle
    DrawMenuBar mhWndForm
End Sub


Sub ShowForm()
    UserForm.Show False
End Sub

 

[Frizzlediam]

I would like to apologize for some of my terrible sentences. As I was going through it and making changes I was also updating the page to reflect them. Before I could get all of the terribleness fixed I accidentally hit post rather than update. Sigh.