Sheets Array

Posted by Sam on January 09, 2002 4:35 PM

Hi
is there any way disable the ability to select multiple shhets in a workbok. I have macros that run when data is changes but it tripps out if more than one sheet is selected.

Thanks

Posted by Russell Hauf on January 09, 2002 4:58 PM

How about putting a few lines at the top of your macro like so:

If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "Please have only one sheet selected then re-run this macro"
Exit Sub ' kick out of the macro
End If

or,

If ActiveWindow.SelectedSheets.Count > 1 Then
Sheets("MySheet").Select
' you could also put Sheets(1).Select
End If

I hope this gives you some ideas,

Russell

Posted by Sam on January 09, 2002 5:44 PM

What happens is when you put a time in like 1330-2130 the code will run on the sheet_change event, unprotect the sheet. calculate the total hours in this case 8 and put that in the target.cell.offset(1,0) then reprotect. When multiple sheets are selected I get the unprotect method error. If you have any more ideas? Is there a way to get the names of all the selected sheets? Then I could unlock them all and this might do the trick.

Thanks

Posted by Juan Pablo G. on January 10, 2002 9:53 AM

This macro produces an array with the names of each selected sheet.

Sub Test()
Dim Nm
Dim i As Integer
i = 1
ReDim Nm(1 To ActiveWindow.SelectedSheets.Count)
For Each sh In ActiveWindow.SelectedSheets
Nm(i) = sh.Name
i = i + 1
Next sh

End Sub

Hope that helps

Juan Pablo G. What happens is when you put a time in like 1330-2130 the code will run on the sheet_change event, unprotect the sheet. calculate the total hours in this case 8 and put that in the target.cell.offset(1,0) then reprotect. When multiple sheets are selected I get the unprotect method error. If you have any more ideas? Is there a way to get the names of all the selected sheets? Then I could unlock them all and this might do the trick. : How about putting a few lines at the top of your macro like so