Results 1 to 9 of 9
  1. #1
    Ron Hobway
    Guest

    Selection (English)

    Hi,

    In Excel it is possible to select multiple sheets in a workbook. You can also save the file having multiple sheets selected within a workbook. I want this last to be impossible, so when a user saves the Excel-file some VBA has to check wether multiple sheets are selected, and if yes, stop that before saving (and for instance, select sheet 1)

    Hope somebody can help me out on this one!

    best regards, Ron

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selection (English)

    Ron,

    You can advantage of the Workbook_BeforeSave event to achieve what you want. Place the following code int the Workbook object of the particular project you want it to apply to. This code selects the first worksheet, thus overriding any existing sheet selections.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWorkbook.Worksheets(1).Select
    End Sub

    If you right click on a sheet tab, you should see the VB editor. Double click on the Thisworkbook (assuming that the you only have the appropraite workbook open) object on the right of the screen, and then paste the above code in the edit window.

    Andrew

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

    Re: Selection (English)

    The following code could be put in the Workbook Before Save event routine.

    <pre>Dim oSheet As Worksheet
    If ActiveWindow.SelectedSheets.Count > 1 Then
    Sheet1.Select
    End If
    </pre>

    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection (English)

    Except you don't need the

    Dim oSheet as Worksheet....


    A slip o' the mouse on a select I suspect...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

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

    Re: Selection (English)

    Correct. That was left over from my first try at getting this to work.
    Legare Coleman

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

    Re: Selection (English)

    Actually, I just thought of a better way to do this. The following code, place in the Before Save Event will leave the active sheet the as the active sheet but remove any multiple sheet selection:

    <pre> ActiveSheet.Select
    </pre>


    Of course, if you don't put in some comments to explain it, it will drive anyone else looking at the code crazy trying to figure out why it is there.
    Legare Coleman

  7. #7
    Ron Hobway
    Guest

    Re: Selection (English)

    Hi,

    Thanks for the quick responses. Just, they dont work. They make sheet 1 te selected one, but they don't deselect all the others. Maybe i wasnt specific about that, but when saved the workbook should only have sheet 1 selected ("active") and all others not.

    Hope you can fix it.

    Thank you very mucht, Ron

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

    Re: Selection (English)

    That is exactly what it does on my Excel 97.

    Are you sure that the code is being executed? Where are you putting the code? The code should go in the Workbook_BeforeSave routine which is in the module that is behind the ThisWorkbook object. To find this routine, go to the VB Editor and double click on the ThisWorkbook object in the project explorer. The in the drop down list on the left side of the edit window select Workbook. Then in the dropdown list on the right select Before save. Now in the code are you should have the following:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    End Sub
    </pre>


    Put the code between those two lines.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selection (English)

    Question:

    Do you even want the other sheets in the workbook? If not, removing them would solve the problem too. Just a thought...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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