PDA

View Full Version : [SOLVED] Make Selection on sheet1 that will duplicate on all worksheets



RonNCmale
04-10-2010, 10:27 PM
I've almost completed this project. I now need to find a way to fill in Sheet1 as Subject, Non Subject, Day, Evening, Night, once filled on Sheet1 it will place a check mark in the appropriate boxes on all the work sheets that corresponds with that selection. I've attached the file that I'm working with.

mdmackillop
04-11-2010, 02:45 AM
Hi Ron
This works on weeks 1 to 4, but your checkboxes in wk5 have different names. If you can get your naming consistent, this method should work



Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim i As Long, j As Long
Set rng = Range("B9:B13")
If Not Intersect(Target, rng) Is Nothing Then
For i = 1 To 4 '<=== should be 52
For j = 1 To 5
Sheets("wk" & i).DrawingObjects("Check Box " & (28 + j)).Value = Not (rng(j) = "")
Next
Next
End If
End Sub

GTO
04-11-2010, 06:36 AM
Hi All,

I wasn't sure what was to be filled-in on the first sheet, or whether we were looking to put checkboxes there and have the others match val, or...?

Anyways, I hadnn't done much, but thought that rather than going through the sheets, maybe a temp mac to do what Malcom pointed out as to consistent names.


Sub xxx()
Dim wks As Worksheet
Dim shp As Shape

For Each wks In ThisWorkbook.Worksheets
If Not wks.Name = "Sheet1" Then
wks.Unprotect
For Each shp In wks.Shapes
If shp.Type = msoFormControl Then

'Debug.Print shp.Name & vbTab & shp.TopLeftCell.Address & vbTab & _
shp.BottomRightCell.Address
Select Case shp.TopLeftCell.Address(False, False)
Case "E1": shp.Name = "chkSubject"
Case "F1": shp.Name = "chkNotSubject"
Case "B3": shp.Name = "chkDayShift"
Case "C3": shp.Name = "chkSwingShift"
Case "D3": shp.Name = "chkGraveyardShift"
End Select
End If
Next
wks.Protect , True, True, True
End If
Next
End Sub


Mark

RonNCmale
04-11-2010, 06:42 AM
could you upload the "timereport" you got to work. What did you enter on Sheet 1? x, yes, true. Did you place this vba code in a new module, or this workbook? Glad to hear you got it to work on 4 worksheets, That's gives me hope that it can be done.
Thanks in advance

mdmackillop
04-11-2010, 07:30 AM
I used Mark's code to change the checkbox names to Chk1 - Chk5 and added my code to the Sheet1 module.

RonNCmale
04-11-2010, 08:18 AM
Well I'll be a monkey's uncle. Thanks for all the help. It now does all that I want it to do. You guys are the greatest!!!!!