K
kazzy
Hi VBAers,
I have two userforms that I want to use for same worksheet. My
worksheet will contain up to approx. 42 rows. For each row I need to
determine which userform to show based on the following criteria:
Group 1:- If columns W and X contain the word "TRUE" then I want to
show userform1 (frmAMTAddition). For these rows, column Y will also
contain "FALSE".
Group 2:- Else if column Y contain the word "TRUE" I want to show
userform2 (frmAMTModification). For these rows, columns W and X will
also contain "FALSE".
I suspect what will make the coding easier is that the alike rows are
grouped (ie; sorted) together. (This is because they were copied over
to the worksheet in their groups using autofilter).
Thus, as soon as the first row for Group 2 is found there will be no
further Group 1 rows in the worksheet.
I also need to cater for the cases if there are only Group 1' rows or
only Group 2 rows.
Here's what I'm thinking so far but I know it's full of holes & it
doesn't compile. I know that I haven't catered for the case if one of
the user forms isn't showing.
If anybody can put me on the right track I'd be grateful. My VBA
application just keeps growing and is doing my head in!
Sub userformselect()
Dim W As String
Dim X As String
Dim Y As String
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets("Combined")
ws1.Activate
Set W = ws1.Range(W).Value
Set X = ws1.Range(X).Value
Set Y = ws1.Range(Y).Value
If W = "TRUE" Or X = "TRUE" Then
frmAMTModification.Hide
frmAMTAddition.Show
ElseIf Y = "TRUE" Then
frmAMTAddition.Hide
frmAMTModification.Show
End Sub
I have two userforms that I want to use for same worksheet. My
worksheet will contain up to approx. 42 rows. For each row I need to
determine which userform to show based on the following criteria:
Group 1:- If columns W and X contain the word "TRUE" then I want to
show userform1 (frmAMTAddition). For these rows, column Y will also
contain "FALSE".
Group 2:- Else if column Y contain the word "TRUE" I want to show
userform2 (frmAMTModification). For these rows, columns W and X will
also contain "FALSE".
I suspect what will make the coding easier is that the alike rows are
grouped (ie; sorted) together. (This is because they were copied over
to the worksheet in their groups using autofilter).
Thus, as soon as the first row for Group 2 is found there will be no
further Group 1 rows in the worksheet.
I also need to cater for the cases if there are only Group 1' rows or
only Group 2 rows.
Here's what I'm thinking so far but I know it's full of holes & it
doesn't compile. I know that I haven't catered for the case if one of
the user forms isn't showing.
If anybody can put me on the right track I'd be grateful. My VBA
application just keeps growing and is doing my head in!
Sub userformselect()
Dim W As String
Dim X As String
Dim Y As String
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets("Combined")
ws1.Activate
Set W = ws1.Range(W).Value
Set X = ws1.Range(X).Value
Set Y = ws1.Range(Y).Value
If W = "TRUE" Or X = "TRUE" Then
frmAMTModification.Hide
frmAMTAddition.Show
ElseIf Y = "TRUE" Then
frmAMTAddition.Hide
frmAMTModification.Show
End Sub