+ Reply to Thread
Results 1 to 28 of 28

Indexing help needed...

  1. #1
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Indexing help needed...

    Hi there,

    Basically I have got a workbook with lots of worksheets each containing information about a property.

    What I need to do is make a separate worksheet as a sort of welcome/index/contents page so anyone using the workbook can easily select which worksheet they want to view etc

    I was wondering what the best way of doing this would be as I am not too advanced with my excel skills! I was thinking a combo box coupled with a lookup button would do nicely although I am not sure how I can implement this into excel?! Are there any tutorials out there that anyone knows of??

    Thanks for any help

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Hi there,

    Basically I have got a workbook with lots of worksheets each containing information about a property.

    What I need to do is make a separate worksheet as a sort of welcome/index/contents page so anyone using the workbook can easily select which worksheet they want to view etc

    I was wondering what the best way of doing this would be as I am not too advanced with my excel skills! I was thinking a combo box coupled with a lookup button would do nicely although I am not sure how I can implement this into excel?! Are there any tutorials out there that anyone knows of??

    Thanks for any help
    Hi,

    as a start,
    Please Login or Register  to view this content.
    should get you a list of sheets.


    change Private Sub BuildIndex_Click to Macro6 if you want a macro rather than a button.

    I presume you know the .Activate property to a sheet to activate the sheet.

    Let me know how you go.
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    Hi there, & thank you for your response..

    I'm afraid as I said in my original post I am not that great with working with excel and am not entierly sure how to do what you suggested. All I have managed to do so far is copy your code and put it into a window in the Visual Basic Editor...and suprisingly nothing happens on that sheet...

    Just so you know I'm afraid I have not had that much experience with VBA and the work I have done with it was in MS Access...

    If you could provide a more step by step approach I would be most greatful!& i am a fast learner!

    Thanks for your time

  4. #4
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    haha...ok after a bit of a tinker I have managed to get the list thing working!!

    What do you mean by the .activate property though?-i think the list works fine without it!

    Do you know of any more code that would be able to take the end user to the specific sheet if they click on it within the index?

    Cheers

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    haha...ok after a bit of a tinker I have managed to get the list thing working!!

    What do you mean by the .activate property though?-i think the list works fine without it!

    Do you know of any more code that would be able to take the end user to the specific sheet if they click on it within the index?

    Cheers
    Hi,

    certainly, hence the question about .Activate.

    Please Login or Register  to view this content.
    placed in the code for the Worksheet (rightmouse the tab, and 'View Code') will switch sheets on double-click in column B

    Let me know how you go.
    ---

  6. #6
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Talking

    Mate you are a legend!-it works perfectly!

    One more thing though, you wouldnt be able to provide me with code so i can put a button on each sheet to go back to the index could you? and how to get the list macro to work when you open the spreadsheet?!

    Thank you so much for your help!
    Last edited by AshP; 12-08-2006 at 10:03 AM.

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Mate you are a legend!-it works perfectly!

    One more thing though, you wouldnt be able to provide me with code so i can put a button on each sheet to go back to the index could you? and how to get the list macro to work when you open the spreadsheet?!

    Thank you so much for your help!
    Are you sure you want a button, a Macro with a shortcut key will do the thing more easily.

    Tools, Macro, Record,
    go to the Index
    Stop recording, and in the Macro options set a letter so that CTRL/letter or CTRL/Shift/Letter will trigger the macro.

    Let me know how you go.
    ---

  8. #8
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Thumbs up

    OK good thinking, guess it will save me adding a button to each page too!

    One final thing- how do I set the list macro to run when the spreadsheet opens?

    Thanks a bunch for your help mate, top advice most appreciated

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    OK good thinking, guess it will save me adding a button to each page too!

    One final thing- how do I set the list macro to run when the spreadsheet opens?

    Thanks a bunch for your help mate, top advice most appreciated
    Hi,

    In the ThisWorkbook code, a Private Sub WorkBook_Open() event, set the item to be run or tested in there.

    Let me know how you go with that
    ---

  10. #10
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Thumbs up Thanks Bryan

    Thank you once again, navigation system works great!

    Cheers Buddy

    PS Do you know much about MS Outlook?

  11. #11
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Thank you once again, navigation system works great!

    Cheers Buddy

    PS Do you know much about MS Outlook?
    Good to see the Excel work for you, and, probably the best answer you'll ever get is, "enough to stay away from it", however I do use it when Hotmail doesn't seem appropriate, why?
    ---

  12. #12
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    well basically we use it in our office to manage all our emails etc. and whenever i or my colleages send emails out to our clients my boss always wants to be copied into the email (cc the email to him) so he can keep track of whats going on.

    the thing is that its becoming quite tedious having to remember to cc my boss every time i send an email and I was just wondering if there was any way he could be automatically cc'ed when I am sending an email to a client.

    I have googled it but all that comes up are third party apps which you have to pay for...there must be a way you can get outlook to do this wwithout a plug in?!

    PS - I'm not sure if I should start a new thread for this, but you wouldnt know any code for excel to produce a list of different values from accross my different worksheets? i.e. each one of my worksheets has a number in cell F1 which I would like to be compiled into a list on a new worksheet. This list needs to be sorted in order from highest to lowest and next to each number needs to be the name of the corrisponding sheet it was from.
    Last edited by AshP; 12-12-2006 at 10:24 AM.

  13. #13
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    well basically we use it in our office to manage all our emails etc. and whenever i or my colleages send emails out to our clients my boss always wants to be copied into the email (cc the email to him) so he can keep track of whats going on.

    the thing is that its becoming quite tedious having to remember to cc my boss every time i send an email and I was just wondering if there was any way he could be automatically cc'ed when I am sending an email to a client.

    I have googled it but all that comes up are third party apps which you have to pay for...there must be a way you can get outlook to do this wwithout a plug in?!

    PS - I'm not sure if I should start a new thread for this, but you wouldnt know any code for excel to produce a list of different values from accross my different worksheets? i.e. each one of my worksheets has a number in cell F1 which I would like to be compiled into a list on a new worksheet. This list needs to be sorted in order from highest to lowest and next to each number needs to be the name of the corrisponding sheet it was from.
    Hi,

    With Outlook, if there was then the Plug-ins woud't sell.

    On the F1 question, I presume that you mean to exclude the sheet that is the 'new worksheet' from the list, and ddo you want empty or Blank values listed?

    ---

  14. #14
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    well basically we use it in our office to manage all our emails etc. and whenever i or my colleages send emails out to our clients my boss always wants to be copied into the email (cc the email to him) so he can keep track of whats going on.

    the thing is that its becoming quite tedious having to remember to cc my boss every time i send an email and I was just wondering if there was any way he could be automatically cc'ed when I am sending an email to a client.

    I have googled it but all that comes up are third party apps which you have to pay for...there must be a way you can get outlook to do this wwithout a plug in?!

    PS - I'm not sure if I should start a new thread for this, but you wouldnt know any code for excel to produce a list of different values from accross my different worksheets? i.e. each one of my worksheets has a number in cell F1 which I would like to be compiled into a list on a new worksheet. This list needs to be sorted in order from highest to lowest and next to each number needs to be the name of the corrisponding sheet it was from.
    Hi,

    Code for your last request,
    Please Login or Register  to view this content.
    You can record a Macro and replace it's code with that, and assign a shortcut key to the macro.

    Let me know how that goes.
    ---

  15. #15
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Cool

    Hi Bryan,

    Thanks again for your expertise

    Code works great, but what would I have to change so that it does not include the 'index' sheet and a few others (the others being the new property template worksheet and other sheets containing searches similar to this one)

    Cheers

  16. #16
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Hi Bryan,

    Thanks again for your expertise

    Code works great, but what would I have to change so that it does not include the 'index' sheet and a few others (the others being the new property template worksheet and other sheets containing searches similar to this one)

    Cheers
    Hi,


    for a small number of sheets, change

    If ws.Name <> sSumSheet Then

    to

    If ws.Name <> sSumSheet And ws.Name <> "OtherSheet" And ws.Name <> "OtherSheet2" Then

    etc, but if there are many then we need some way to identify them.

    hth
    ---

  17. #17
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Thumbs up

    Cheers mate

    One final thing - how can I exclude a few sheets from the code that generates the index? (Tried the code above but it didnt work)

    I think that should be enough to get this damned database finished!

    Thanks once again for your advice

    Last edited by AshP; 12-15-2006 at 06:17 AM.

  18. #18
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Cheers mate

    One final thing - how can I exclude a few sheets from the code that generates the index? (Tried the code above but it didnt work)

    I think that should be enough to get this database finished!

    Thanks once again for your advice

    Hi, the code above certainly does work, that's how it came to be the code above, what sheetnames do you want to omit?

    try the code as
    Please Login or Register  to view this content.
    and each sheet not indexed will be msgbox to you.

    note, you ned to have an F1Summary sheet and the variables Dim'd as per the previous code.

    ---
    Last edited by Bryan Hessey; 12-15-2006 at 07:42 AM.

  19. #19
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    HI dude, am afriad I'm having a problem getting the code to work..am sure it is a very simple mistake but I really dont know what I'm doing with this VB stuff! This is what I have so far:


    Sub Macro6()
    Dim i As Long
    Dim ws As Worksheet
    Dim sSumSheet As String
    For i = 1 To Worksheets.Count

    Range("D" & i + 10) = i
    Range("E" & i + 10) = Sheets(i).Name



    If ws.Name <> sSumSheet And ws.Name <> "Pop List" Then
    Sheets(sSumSheet).Cells(iSheet, 1).Value = ws.Name
    Sheets(sSumSheet).Cells(iSheet, 2).Value = ws.Cells(1, 6).Value
    iSheet = iSheet + 1
    Else:
    MsgBox ws.Name
    End If
    Next
    '
    ' Macro6 Macro
    ' Macro recorded 08/12/2006 by
    '

    '
    End Sub


    Get an error at the start of the IF statement 'Object variable or With block variable not set'

  20. #20
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    HI dude, am afriad I'm having a problem getting the code to work..am sure it is a very simple mistake
    the code as supplied to you looks different to what you are now showing.
    but I really dont know what I'm doing with this VB stuff! This is what I have so far:


    Sub Macro6()
    Dim i As Long
    Dim ws As Worksheet
    Dim sSumSheet As String
    For i = 1 To Worksheets.Count

    Range("D" & i + 10) = i
    Range("E" & i + 10) = Sheets(i).Name



    If ws.Name <> sSumSheet And ws.Name <> "Pop List" Then
    Sheets(sSumSheet).Cells(iSheet, 1).Value = ws.Name
    Sheets(sSumSheet).Cells(iSheet, 2).Value = ws.Cells(1, 6).Value
    iSheet = iSheet + 1
    Else:
    MsgBox ws.Name
    End If
    Next
    '
    ' Macro6 Macro
    ' Macro recorded 08/12/2006 by
    '

    '
    End Sub


    Get an error at the start of the IF statement 'Object variable or With block variable not set'
    Hi - try
    Please Login or Register  to view this content.
    and always use Option Explicit

    Let me know how that goes.
    ---
    Last edited by Bryan Hessey; 12-18-2006 at 09:57 AM.

  21. #21
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    Hi, have tried the last bit of code you gave me but it returns an 'Subscript out of range' error for line:

    Sheets(sSumSheet).Cells(i, 1).Value = ws.Name

    Just to clarify this macro creates the original index list for my database & I am trying to get it to omit the worksheet 'Pop List' (it will also have to omit some other worksheets in the future, once I have created them)

    Cheers dude

  22. #22
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Hi, have tried the last bit of code you gave me but it returns an 'Subscript out of range' error for line:

    Sheets(sSumSheet).Cells(i, 1).Value = ws.Name

    Just to clarify this macro creates the original index list for my database & I am trying to get it to omit the worksheet 'Pop List' (it will also have to omit some other worksheets in the future, once I have created them)

    Cheers dude
    as supplied it did that. You will need to add other names as you create them.

    You need to check the name of your summary sheet to the name held in the sSumSheet variable, you seem to have a difference there.

    If the name appears ok re-post your code here.

    hth
    ---

  23. #23
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    Quote Originally Posted by Bryan Hessey
    as supplied it did that. You will need to add other names as you create them.
    Ok I get this /\

    Quote Originally Posted by Bryan Hessey
    You need to check the name of your summary sheet to the name held in the sSumSheet variable, you seem to have a difference there.

    If the name appears ok re-post your code here.

    hth
    ---
    This is my code:
    Option Explicit
    Sub Macro6()
    Dim i As Long
    Dim ws As Worksheet
    Dim sSumSheet As String
    sSumSheet = "Index" <-I changed this to 'Index'
    For i = 1 To Worksheets.Count
    Range("D" & i + 10) = i
    Range("E" & i + 10) = Sheets(i).Name
    Set ws = Worksheets(i)
    If ws.Name <> sSumSheet And ws.Name <> "Pop List" Then
    Sheets(sSumSheet).Cells(i, 1).Value = ws.Name
    Sheets(sSumSheet).Cells(i, 2).Value = ws.Cells(1, 6).Value
    i = i + 1
    Else:
    MsgBox ws.Name
    End If
    Next
    '
    ' Macro6 Macro
    ' Macro recorded 08/12/2006 by
    '

    '
    End Sub

    The macro now runs without errors, unfortunatly it produces some weird results - it only shows every other number/worksheet name in the index i.e. 1,2,4,6,8,10etc & it also seems to run the population macro in columns a&b also missing out every other worksheet - whats going on!?

    Also when the macro is run a message box appears saying 'Index' - i thought this message box was supposed to show the worksheets that were left out?!

    Cheers dude & Merry XMas!

  24. #24
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Ok I get this /\



    This is my code:
    Option Explicit
    Sub Macro6()
    Dim i As Long
    Dim ws As Worksheet
    Dim sSumSheet As String
    sSumSheet = "Index" <-I changed this to 'Index'
    For i = 1 To Worksheets.Count
    Range("D" & i + 10) = i
    Range("E" & i + 10) = Sheets(i).Name
    Set ws = Worksheets(i)
    If ws.Name <> sSumSheet And ws.Name <> "Pop List" Then
    Sheets(sSumSheet).Cells(i, 1).Value = ws.Name
    Sheets(sSumSheet).Cells(i, 2).Value = ws.Cells(1, 6).Value
    i = i + 1 Else:
    MsgBox ws.Name
    End If
    Next
    '
    ' Macro6 Macro
    ' Macro recorded 08/12/2006 by
    '

    '
    End Sub

    The macro now runs without errors, unfortunatly it produces some weird results - it only shows every other number/worksheet name in the index i.e. 1,2,4,6,8,10etc & it also seems to run the population macro in columns a&b also missing out every other worksheet - whats going on!?

    Also when the macro is run a message box appears saying 'Index' - i thought this message box was supposed to show the worksheets that were left out?!

    Cheers dude & Merry XMas!
    Hi,

    first thing noticed, you are using

    For i = 1 To Worksheets.Count

    and then incrementing i in the middle, thus you miss a sheet each time you do that.


    re-test with the correct sheets processsing, it should never have been there
    ---
    added - reply to your Also when the macro is run a message box appears saying 'Index' - i thought this message box was supposed to show the worksheets that were left out?! is your
    sSumSheet = "Index" <-I changed this to 'Index'

    ---
    Last edited by Bryan Hessey; 12-21-2006 at 09:35 AM.

  25. #25
    Registered User
    Join Date
    12-06-2006
    Posts
    17
    Happy New Year!

    Am almost there with this list, this is my code now:

    Option Explicit
    Sub Macro6()
    Dim i As Long
    Dim ws As Worksheet
    Dim sSumSheet As String
    sSumSheet = "Index"
    For i = 1 To Worksheets.Count
    Range("D" & i + 10) = i
    Range("E" & i + 10) = Sheets(i).Name
    Set ws = Worksheets(i)
    If ws.Name <> sSumSheet And ws.Name <> "Pop List" Then
    Sheets(sSumSheet).Cells(i, 1).Value = ws.Name
    Sheets(sSumSheet).Cells(i, 2).Value = ws.Cells(1, 6).Value

    End If
    Next
    '
    ' Macro6 Macro
    ' Macro recorded 08/12/2006 by
    '

    '
    End Sub

    As you will notice I have removed the message box and i = i + 1 from the code as you suggested. What happens now is a little strange but almost right...

    The index list is created (in columns D&E which is correct) but does not leave out 'Index' & 'Prop List'. In addition to this however, columns A&B are filled with the list of properties excluding 'Index' & 'Prop List' together with the population figures?! Whats going on?
    Cheers

  26. #26
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Happy New Year!

    Am almost there with this list, this is my code now:

    Option Explicit
    Sub Macro6()
    Dim i As Long
    Dim ws As Worksheet
    Dim sSumSheet As String
    sSumSheet = "Index"
    For i = 1 To Worksheets.Count
    Range("D" & i + 10) = i
    Range("E" & i + 10) = Sheets(i).Name
    Set ws = Worksheets(i)
    If ws.Name <> sSumSheet And ws.Name <> "Pop List" Then
    Sheets(sSumSheet).Cells(i, 1).Value = ws.Name
    Sheets(sSumSheet).Cells(i, 2).Value = ws.Cells(1, 6).Value

    End If
    Next
    '
    ' Macro6 Macro
    ' Macro recorded 08/12/2006 by
    '

    '
    End Sub

    As you will notice I have removed the message box and i = i + 1 from the code as you suggested. What happens now is a little strange but almost right...

    The index list is created (in columns D&E which is correct) but does not leave out 'Index' & 'Prop List'. In addition to this however, columns A&B are filled with the list of properties excluding 'Index' & 'Prop List' together with the population figures?! Whats going on?
    Cheers
    HI,

    that's what it is designed to do

    Please Login or Register  to view this content.
    remove either the red code or the blue code to include/exclude the two named sheets in the index.

    hth
    ---

  27. #27
    Registered User
    Join Date
    12-06-2006
    Posts
    17

    Thumbs up

    Cheers buddy, thanks for all your help

    Sorry for being such a noob at times!

  28. #28
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by AshP
    Cheers, thanks for all your help

    Sorry for being such a noob at times!
    just so long as it works for you now.

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1