Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro help (Excel 97)

    I am working on a workbook and need help in 3 different areas. #1 Is it possible to link one worksheet to another by saying that if there is a date in cell A4, it is to insert a row, insert the date and in the next column, insert the name of the sheet, either from the sheet tab or cell A1? #2 Is there a way to make the workbook go to a certain sheet and then close after someone saves it? #3. Can you make it so that if someone types to the end of the cell, it will automatically expand so it can be read by others? If anyone could help me on any of these problems, I would really appreciate it. Thank you.

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

    Re: Macro help (Excel 97)

    Based on your subject, I as assuming that you are asking how to do these in VBA.

    1- Question #1 is almost certainly possible, but we would need some clarification and additional information to show you how to do this. You talk about linking two sheets, but then all of your references to cells don't say which sheet the cell is on. You say check cell A4 to see if it contains a date. Can this cell contain something else, and are you asking how to determine if the content is a date or something else, or are you asking how to determine if cell A4 is not empty? You say you want to insert a row. On which sheet do you want to insert that row, and where on the sheet? Are you really talking about just cell A4, or do you really want to loop down the column looking at more than one cell?

    2- Do you want to "go to" this particular sheet after the user does the save so that this sheet will be the active sheet when the workbook is opened again, or do you want some other event to trigger the "go to" this sheet? Do you want to just close the workbook, or do you want to shut Excel down? What do you want to do if the user does a SaveAs?

    3- Do you want to do this for all cells on all sheets? Is it OK if the column shrinks if the longest entry is shorter than the column length?
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    Thank you so much for responding.
    #1 - There will be one sheet called Event Log where I would like all entries made on the other worksheets to appear in cell A4, just as the date they were entered and the name of the worksheet that it was made on. Each time a new entry is made, I would like it to insert a line so as to appear at the top of last entry. The only time an entry would need to be made is if someone puts a date in cell A4 on one of the other worksheets (indicating data was going to be input). Otherwise nothing needs to be entered, because the cell will remain blank and never have anything else in it. For the date, each entry (the one on the Event Log page and on the rest of the worksheets) will be in cell A4. Then if a date is entered, I would like it to pick up the name of the sheet, which is on the sheet tab and also in A1. It doesn't matter where it gets it to me. I would like that name to appear in A5.
    #2 What I want to do is when a person clicks on the Save button, have it open the Event Log page so it will be the active sheet when this workbook is opened again, and then close Excel. Please don't confuse me anymore. No one will do a Save As. You know people always do exactly as they are instructed. (ha)
    #3 I just want to do it in one cell on all the sheets, except for the Event Log. I would prefer nothing shrinks.
    Again, I really appreciate your help.

  4. #4
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    You are so wonderful! I will try this at work on the workbook. At least I will have a starting place. That's mainly what I needed. Thank you, thank you!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I'll let you know how it turns out.

  5. #5
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    It's me again. I'm sorry, but I just looked in my Excel at home and I can't find anything called Workbook Change or ThisWorkbook object. I only know a little about Visual Basics. I thought it would be easier to find. Can you tell me where to look? Please. Thank you again. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Macro help (Excel 97)

    Go to the VBE (Visual Basic Editor) by pressing Alt+F11. The project explorer should be displayed on the left of the screen. Find your workbook in the list of projects. If the VBA project is not expanded, click on the plus sign next to it. If the "Microsoft Excel Objects" is not expanded, click on the plus sigh next to it. That should expose a list of the Sheets in the workbook, and at the end the ThisWorkbook object. Right click on the ThisWorkbook object and select "View Code" from the pop up menu. Now, in the right hand window, click on the arrow on the left drop down list. Select Workbook from the list. Click on the arrow in the right hand drop down list and select the event routine you are looking for (SheetChange or BeforeSave for the code I sent to you. This should display a dummy routine for that event.
    Legare Coleman

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

    Re: Macro help (Excel 97)

    I am still not 100% clear on what you are trying to do. However, I will give you some code below that might get you started.

    1- You should be able to use the Worksheet Change event to do this. The code below has not been tested since I don't have your workbook, and don't completely understand what you are doing. Again, this code goes in the Workbook Change event routine in the module behind the ThisWorkbook object, not in a general module.

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "Event Log" Then 'See if change on sheet other than Event Log
    If Not Intersect(Target, Range("A4")) Is Nothing Then ' See if change is in cell A4
    If Sh.Range("A4") <> "" Then ' See if A4 contains something
    Application.EnableEvents = False
    Worksheets("Event Log").Range("A5").EntireRow.Insert shift:=xlShiftDown ' Insert row above row 5
    Worksheets("Event Log").Range("A5").Value = Sh.Name 'Insert sheet name in A5
    Application.EnableEvents = True
    End If
    End If
    End If
    End Sub
    </pre>


    2- The following code placed in the Workbook BeforeSave event routine should get you started on this question. Again I have not tested this code. I have also indicated where you must put code that does whatever you want to do if the user clicks SaveAs.

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveAsUI Then
    Cancel = True
    Worksheets("Event Log").Activate
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    Application.Quit
    Else
    'do whatever you want to do if the user clicks SaveAs here
    End If
    End Sub
    </pre>


    3- The following code, placed in the Worksheet Change event routing (same routine as in answer #1), will do an Autofit on column A after anyone changes cell A4. However, this will also shrink the column width. Trying to calculate if what is displayed in a cell fits is extremely complicated, and it is even more complicated to calculate what column width is need for what is there to fit.

    <pre> If Sh.Name <> "Event Log" Then 'See if change on sheet other than Event Log
    If Intersect(Target, Range("A4")) <> Nothing Then ' See if change is in cell A4
    Target.Range("A4").EntireColumn.AutoFit
    End If
    End If
    </pre>

    Legare Coleman

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

    Re: Macro help (Excel 97)

    I also just edited my second message in this thread to fix a problem in the third line of the first macro code in the message.
    Legare Coleman

  9. #9
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    Thank you again for all your help! It is wonderful having an expert to turn to. Will let you know how it turns out as soon as I can.

  10. #10
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    Hi again. I couldn't get that to work, except for the last one, at least a little. I wanted it to go to the Event Log sheet to make it the active sheet when it was opened again and then close Excel when someone clicked on Save. But it brought up the Save As dialog box. If you clicked Cancel it would then close Excel, but did not go to the other sheet when it was reopened. I tried to attach a copy of the complete workbook but it said it was too big. I made a smaller version, that was 99k, but it still said it was too big. Do you or anyone else have any more suggestions? I really do appreciate the time and effort. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

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

    Re: Macro help (Excel 97)

    As I said, I did not test my code, and it does have a problem. I have tested the following code, and it does seem to work:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveAsUI Then
    Cancel = True
    Worksheets("Event Log").Activate
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ThisWorkbook.Save
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Quit
    Else
    'do whatever you want to do if the user clicks SaveAs here
    End If
    End Sub
    </pre>


    Again, that code only does something when Save is selected. If SaveAs is selected, it does nothing if you don't replace my comment after the Else with some code.

    By The Way, If you are only trying to get the workbook to open to a specific sheet, then the following code in the workbook open event routine would be a much better way to do that:

    <pre>Private Sub Workbook_Open()
    Worksheets("Event Log").Activate
    End Sub
    </pre>

    Legare Coleman

  12. #12
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    Thank you again. I will try it at work. At home, I have Office 2000 and it was created in 97 at work, so I don't think I can try it here.
    <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  13. #13
    Lounger
    Join Date
    Mar 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (Excel 97)

    I just wanted to let you know that the last thing you gave me worked like a charm. I was able to make it go to another sheet after clicking Save. You are the greatest! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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