Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    auto name sheet (Excek 2000 9.0.2720)

    Hi and thanks again in advance. Two questions, the first relating to an earlier posting. I had asked for assistance in developing a macro that would automatically rename a sheet based on cell contents. The cell contents were based on a reference from a combo list that allowed a user to select a name. The name was then placed as cell a1 on a sheet being used for a summary. I was originall provided with the following code.
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5")) Is Nothing Then
    On Error Resume Next
    Me.Name = Range("a5")
    End If
    End Sub
    However I couldn't seem to make it work. After some further research, the following code allowed me to rename the sheet as required, but I had to manually make the macro run.
    Sub rename()
    Dim CELLREF As String
    CELLREF = Sheets(3).Range("a1").Value
    Sheets(3).Name = CELLREF
    End Sub
    Is there a way I can make the macro run automatically so that when a user fills in the "collection" spreadsheet (including selecting from the combo list) the summary sheet will be renamed to the cell contents without any further user intervention? Thanks again for helping out us newbies who know just enough to be dangerous.
    This leads to my second question. While I highly value the support provided here, are there any recommendations to a newbie as to any books to help learn VBA in excel. I'm a good self learner, and comfortable with trying things out.
    Sorry for the long winded post. Appreciative of any and all help. The people here have been great.
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    Last question first:

    See <post#=539,691>post 539,691</post#> for some book recommendations.

    How to use Visual Basic for Applications in Excel contains links to Microsoft articles about Excel VBA.

    If you search Google for excel vba tutorial you'll find many online tutorials.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    Could you provide some more info? Do you want to rename only one worksheet, or a different worksheet depending on which cell the user changes? Which cell or cells are involved?

  4. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    Thanks for the reply. The work book is made up of three sheets. One sheet is the "controls" sheet, basically consisting of a list of names. The user fills in one sheet (demographicswork sheet) with information on their organization, eg. membership numbers. The combo list on this sheet allows them to pick the name of their organization. The final sheet is a summary sheet that summarizes the information provided by the user on the sheet (demographics work sheet) they filled in. The name chosen from the combo list on sheet one is put into cell a1 on the summary sheet. I would like the summary sheet to be named the name of the organization chosen by the user, which is then automatically placed in cell a1 by a formula referencing the demographics work sheet . It takes the name from demographics worksheet cell g5 which itself is from controls c1. Does any of that make sense? Note Multiple users will be completing the demographics worksheet (each one completing their own spreadsheet)

    In short, I would like the summary sheet to be named as the name chosen by the user on the worksheet and placed into the summary sheet without any further input from the user. ie. the user will not name the sheet.

    Thanks for the assistance, and I'm off to check out the site you mentioned. I have googled, but was looking for any advice from minds wiser than mine as to which are great books.
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    In which cell on which worksheet does the user select or enter the name to be used for the summary worksheet? If I read your description correctly, it iscell G5 on the demographics worksheet, but I could be mistaken.

    Assuming I am right, you can do the following:
    - Activate the demographics worksheet.
    - Right-click the sheet tab.
    - Select 'View code' from the popup menu.
    - Note the name of the summary sheet in the project explorer on the left hand side. You'll see something like Sheet3 (Summary). You'll need the first name, before the parentheses - Sheet3 in this example.
    - Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G5")) Is Nothing Then
    If Not Range("G5") = "" Then
    ' Use the name noted above
    Sheet3.Name = Range("G5")
    End If
    End If
    End Sub

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    It would be a lot easier to help with your problem if you would upload the workbook showing what you have (you could delete everything except what is necessary for renaming the sheet). I have attached a workbook that uses data valadation to select the organization name and then put that name in cell A1 on the summary sheet and renames that sheet to the organization name using the worksheet change event routine for the demographicswork sheet.
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    I would be happy to post the attachment, however even after I have zipped it, it is still aprox. 281 kb. I don't know how to get it to the size that would meet the size limitation. sorry.

    We are involved with a number of sport organizations. We are trying to collect demographic data from them regarding, membership, participants, coaches, athletes, officials etc. We use the demographic worksheet to allow them to input the data. Each sport is sent the workbook by email, and they are asked to complete and send back. We utilize the summary information as part of determining funding. We compile a summary workbook, based on all of the summary sheets provided. Again, the people completing the demographics worksheet have varying degrees of computer literac;y.

    Any help is greatly appreciated.

    Note: We are a non profit organization charged with the development of amateur sport in our province.

    Thanks
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    Can you make a small example workbook that has the relevant features to illustrate the issues and attach that?

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    You should be able to delete everything from the workbook except what is needed to show how you want to select the organization name and have it rename the worksheet. Did the workbook I previously uploaded come even close?
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    Ok. I think I've reduced the file so that it is useable. It is attached.

    What I would like to be able to do, using the spreadsheet, is select Archery as the sport and have the summary page, currently titled as athletics, automatically be renamed to Archery. Hopefully I've included everything that you need.

    Again, a very large thank you to your responses and patience. It is much appreciated. Hopefully, when I get more conversant with the programming end of things (excel vba) I too will be able to offer assistance.

    Thanks
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  11. #11
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    In reply to your question on the spreadsheet you provided. Yes, it does seem to do what I need. Automatically renaming the worksheet, but I'm not sure how you did it. I see that you listed the org. (we have over 100) and that you have some sort of drop down list to pick from (I like the way that works, but don't know how to do it) and I need that "picked org" to be populated onto the summary sheet in a "title" as well as being used ot rename the worksheet.

    Is everything as clear as mud.

    Think I need to go back to school and learn how to do things properly. Thanks again.
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    To see how the dropdown in demographicswork!B1 has been set up, click in this cell, then select Data | Validation.
    The 'Allow' dropdown contains List, and the Source box contains =$A$1:$A$20 - this is the list that populates the dropdown in B1.

    To see how the sheet name gets changed, right-click the sheet tab of demographicswork and select View Code from the popup menu.
    The Worksheet_Change event procedure is run each time the user changes the value of one or more cells. The code checks whether B1 has changed and whether B1 is not empty. If so, it sets the value of cell A1 on the 3rd sheet, and changes the name of the 3rd sheet.
    For this to work, the code MUST be in the code module belonging to the worksheet, and it MUST be defined as

    Private Sub Worksheet_Change(ByVal Target As Range)

  13. #13
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    Thank you , Thank you, Thank you. I have utilized the code and instructions provided by both HansV and LegareColeman and now have a working workbook that seems to do what I want it do. One final question (I hope) in the drop down list (pick from list) is there anyway of increasing the size of the text. Presently it is approx. an 8 point font and I would like it to be about 12 or 14. In the source area, I have made it a fourteen, but that doesn't seem to have any effect on the list. Any suggestions.

    And again, thanks so much. I even basically understand what is happening. You both rock!!!!
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    The Validation dropdown list uses a fixed font. The only way to change the displayed size is to change the zoom percentage of the worksheet.
    The same goes for the combo box (dropdown list) from the Forms toolbar. If you really need a larger font size, use the combo box from the Control Toolbox. This allows you to specify a font and a font size.

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto name sheet (Excek 2000 9.0.2720)

    I have attached a workbook that I think does what you want. To change the sport, click in the area where the current sport is shown and you will get a listbox that you can use to select a new sport. If you add additional sports, you will need to change the defined name Sports to the new range that defines the sports. Hans has explained how to find out how it works.
    Legare Coleman

Posting Permissions

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