Spreadsheet Help

[Steve Voltage]

I am trying to adapt Gary's sample "refresh quick pick list" spreadsheet so that sheets 1-4 refresh using -3.1 and sheets 5-6 using -3.2. At present the sheet only populate Q2 in sheet 1, any help please ?

http://gruss-software.co.uk/forum/viewtopic.php?t=3558

[osknows]

Just copy the exisitng code into each sheet object in the vba editor. Say you have 10 sheets, you need the worksheet_change event in all 10 sheets

If you want to update all sheets based on a change in only one sheet use the code below (I don't think this is what you want though)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            For Each ws In Worksheets

            ws.Range("Q2").Value = -3
            Next
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                For Each ws In Worksheets
                ws.Range("Q2").Value = -5
                Next
            End If
        End If
        Application.EnableEvents = True
    End If
End Sub


[Steve Voltage]

Just copy the exisitng code into each sheet object in the vba editor. Say you have 10 sheets, you need the worksheet_change event in all 10 sheets

If you want to update all sheets based on a change in only one sheet use the code below (I don't think this is what you want though)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            For Each ws In Worksheets

            ws.Range("Q2").Value = -3
            Next
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                For Each ws In Worksheets
                ws.Range("Q2").Value = -5
                Next
            End If
        End If
        Application.EnableEvents = True
    End If
End Sub


Thanks mate will try later.

Just copy the exisitng code into each sheet object in the vba editor. Say you have 10 sheets, you need the worksheet_change event in all 10 sheets

I have tried this but couldnt get it to work. I'll have a go with the other code.

[osknows]

Actually, thinking about it more it may not work. Depending on the refresh rate it could be the case that excel gets confused as to worksheet to update first when multiple events fire close together

I'll look into it and get back to you....

[osknows]

After a bit of consultation (even I need help on this!) the advice given is....

1. Consider each sheet as a table. I would not have multiple processes updating the same sheet.
2. I would aggregate or report the data from the tables on a separate sheet. Hopefully there is a data/time stamp on the tables. I would periodically perform this aggregation using a criteria that would return the subset of data of interest. I would NOT use events in the table sheets.

Therfore, if you want to populate Q2 across all sheets at the same time then previous code is fine. If you want to populate Q2 independently across multiple sheets then feed some trigger from each of the sheets into a single sheet and have 1 worksheet_change event

For example, for 4 sheets you could set up a 2x4 table in sheet1 which reads
Col Z - Col AB
Sheet(1) - 07:32:04
Sheet(2) - 02:45:54
Sheet(3) - 01:34:01
Sheet(4) - 12:34:01

Where the times are cell D2 in each sheet.

Then on 1 worksheet_change event you can test if the time has changed since the last refresh and apply changes to each sheet named in col AB

DOes that make sense?
Os

[osknows]

or maybe this will be better placed in the workbook object??

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If triggerQuickPickListReload Then
            triggerQuickPickListReload = False

            Sh.Range("Q2").Value = -3
       
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False

                Sh.Range("Q2").Value = -5
 
            End If
        End If
        Application.EnableEvents = True
    End If
End Sub