Reducing workbook size.

[andytpl]

I have this workbook with about ever increasing worksheets. On every worksheet is a worksheet selection change event routine that I am trying to transfer or alter into a module sheet and then have a sub routine to call this selection change event routine to save on file size. The question how will the code in the module and individual sheet look like to accomplish what I want. I have below the present codes to give you an idea.

Thanks in advance for any help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim frmUF As UserForm1
Dim frmUF2 As UserForm2
Dim frmUF3 As UserForm3
Dim frmUF4 As UserForm4
Dim lRowEnd As Long
Dim sDataRange As String

If Not Intersect(Target, Range("$K$4:$N$4")) Is Nothing Then
Set frmUF = New UserForm1
With frmUF
.SelectionDataList = "Menu!Risk_Likelihood2"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Range("$K$5:$N$5")) Is Nothing Then
Set frmUF2 = New UserForm2
With frmUF2
.SelectionDataList = "Menu!Risk_Impact2"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Range("$G$6:$I$6")) Is Nothing Then
Set frmUF3 = New UserForm3
With frmUF3
.SelectionDataList = "Menu!Action_Status"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Range("$I$12:$N$12")) Is Nothing Then
Set frmUF4 = New UserForm4
With frmUF4
.SelectionDataList = "Menu!Risk_Response"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With
End If
End Sub

[Bob Phillips]

Change it to a Workbook_SheetSelectionChange event, one only then.

[andytpl]

xld,

I change the code as follows and put into the This workbook page but don't seem to get it to work

Private Sub Workbook_SheetChange(ByVal Target As Range)
Dim frmUF As UserForm1
Dim frmUF2 As UserForm2
Dim frmUF3 As UserForm3
Dim frmUF4 As UserForm4
Dim lRowEnd As Long
Dim sDataRange As String

If Not Intersect(Target, Range("$K$4:$N$4")) Is Nothing Then
Set frmUF = New UserForm1
With frmUF
.SelectionDataList = "Menu!Risk_Likelihood"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Range("$K$5:$N$5")) Is Nothing Then
Set frmUF2 = New UserForm2
With frmUF2
.SelectionDataList = "Menu!Risk_Impact"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Range("$G$6:$I$6")) Is Nothing Then
Set frmUF3 = New UserForm3
With frmUF3
.SelectionDataList = "Menu!Action_Status"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Range("$I$12:$N$12")) Is Nothing Then
Set frmUF4 = New UserForm4
With frmUF4
.SelectionDataList = "Menu!Risk_Response"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With
End If
End Sub

[Bob Phillips]

I think I said the SheetSelectionChange event not the SheetChange event ....

AND ... you have to get the signature correct, workbook sheet events use the sh argument ...

AND ... you need to use the sh argument in the ranges that you address as they exist on all sheets



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim frmUF As UserForm1
Dim frmUF2 As UserForm2
Dim frmUF3 As UserForm3
Dim frmUF4 As UserForm4
Dim lRowEnd As Long
Dim sDataRange As String

If Not Intersect(Target, Sh.Range("$K$4:$N$4")) Is Nothing Then
Set frmUF = New UserForm1
With frmUF
.SelectionDataList = "Menu!Risk_Likelihood"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Sh.Range("$K$5:$N$5")) Is Nothing Then
Set frmUF2 = New UserForm2
With frmUF2
.SelectionDataList = "Menu!Risk_Impact"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Sh.Range("$G$6:$I$6")) Is Nothing Then
Set frmUF3 = New UserForm3
With frmUF3
.SelectionDataList = "Menu!Action_Status"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, Sh.Range("$I$12:$N$12")) Is Nothing Then
Set frmUF4 = New UserForm4
With frmUF4
.SelectionDataList = "Menu!Risk_Response"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With
End If
End Sub

[andytpl]

Sorry, this is beyond me. The above codes were provided to me I just cut and paste into the worksheet but it getting too big a file to handle which led me to think about reducing the file size. I will appreciate very much if you could show how your suggestion can be done.
Thanks

[Bob Phillips]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[andytpl]

Thanks! :bow:

One final question of this subject. If not all worksheets in this workbooks are suppose to be subject to this sub routine, what will I need to do to get it in order. :think:

[andytpl]

I found the answer. I add this statement into the codes just after the declaration portion
If sh.Name Like "SWM*" Then

[Bob Phillips]

I don't understand what you wanted or what you did. Can you explain, if only for the archives?

[andytpl]

I wanted to reduce the file size of this workbook and one way is change the worksheet selection change to a workbook event. This selection change event is only applicable to those worksheets of this workbook with sheetname starting with SWM. My first hurdle is to change from worksheet to workbook selection change event and then applying only to those worksheet with name starting with SWM. Below is the final codes residing in the "This Workbook" module space

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)

Dim frmUF As UserForm1
Dim frmUF2 As UserForm2
Dim frmUF3 As UserForm3
Dim frmUF4 As UserForm4
Dim lRowEnd As Long
Dim sDataRange As String

If sh.Name Like "SWM*" Then

If Not Intersect(Target, sh.Range("$K$4:$N$4")) Is Nothing Then
Set frmUF = New UserForm1
With frmUF
.SelectionDataList = "Menu!Risk_Likelihood"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, sh.Range("$K$5:$N$5")) Is Nothing Then
Set frmUF2 = New UserForm2
With frmUF2
.SelectionDataList = "Menu!Risk_Impact"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, sh.Range("$G$6:$I$6")) Is Nothing Then
Set frmUF3 = New UserForm3
With frmUF3
.SelectionDataList = "Menu!Action_Status"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With

ElseIf Not Intersect(Target, sh.Range("$I$12:$N$12")) Is Nothing Then
Set frmUF4 = New UserForm4
With frmUF4
.SelectionDataList = "Menu!Risk_Response"
.Show
If .SelectedValue <> "" Then Target.Value = .SelectedValue
End With
End If
End If

End Sub

[Bob Phillips]

I see, I don't remember you saying it applied to certain sheets, I thought you said all sheets.

But no matter, it is solved, and best of all, you got the last piece yourself.