I'm writing a VBA form which looks at a sheet of data and narrows down a selection based on drop boxes selected. The data sheet contains the options for each drop box under different headings eg
Drop box 1 - Please select your company -> narrows down contents of drop box 2
Drop Box 2 - Please select your department -> narrows down contents of drop box 3
Drop Box 3 - Do you wish to raise or approve -> narrows down to a selectable list
I have the above working, utilizing scripting dictionaries and a lot of nestled if statements, as per the below. Basically this populates the dictionary with any rows which match the relevant criteria.
Is there a neater way of doing this? It means reviewing the full criteria each time you get into the nestled if statement, so the 'If Not Dic.Exists(rCell.Value) And rCell.Value...' statement just gets longer and longer under each if statement.
I'm wondering if there is a way to have the componants of that line embedded as variables eg If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And Statement1 And Statement2
where
Statement 1 = rCell.Offset(0, -2).Value = CompanySelect.Value
Statement 2 = rCell.Offset(0, 1).Value = "Display access"
I could then change the statements based on the different If values so if we were looking at display access it would be the above, but for approving it would be
Statement 1 = rCell.Offset(0, -2).Value = CompanySelect.Value
Statement 2 = rCell.Offset(0, 1).Value = "Approval"
Current code:
If CompanySelect.Value <> "" Then
If SAPDisplay.Value = False And SRMUse.Value = False And SAPEdit.Value = False Then
RoleSelector.RoleList.Clear
For Each rCell In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, -2).Value = CompanySelect.Value Then
Dic.Add rCell.Value, Nothing
End If
Next rCell
For Each Key In Dic
RoleSelector.RoleList.AddItem Key
Next
Dic.RemoveAll
ElseIf SAPDisplay.Value = True Then
If SRMUse = False Then
RoleSelector.RoleList.Clear
For Each rCell In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, 1).Value = "Display access" And rCell.Offset(0, 2).Value = "N/A" Then
Dic.Add rCell.Value, Nothing
End If
Next rCell
For Each Key In Dic
RoleSelector.RoleList.AddItem Key
Next
Dic.RemoveAll
ElseIf SRMUse = True Then
If SRMDisplay.Value = False And SRMCreate.Value = False And SRMApprove.Value = False Then
RoleSelector.RoleList.Clear
For Each rCell In ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))
If Not Dic.Exists(rCell.Value) And rCell.Value <> "" And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, -2).Value = CompanySelect.Value And rCell.Offset(0, 1).Value = "Display access" And rCell.Offset(0, 2).Value <> "N/A" Then
Dic.Add rCell.Value, Nothing
End If
Next rCell
For Each Key In Dic
RoleSelector.RoleList.AddItem Key
Next
Dic.RemoveAll
Aucun commentaire:
Enregistrer un commentaire