With 2 userforms, having code that does not interact between the two

Question

I have a userform1 named "srForm", that when save commandbutton is clicked will put the data in my main worksheet("srData"). In the first column "A" a unique ID will be placed generate by the userform_initialize.
The userform_initialize code is as followed:

Private Sub UserForm_Initialize()
Me.srID.Enabled = True
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("srData")
'find last data row from database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
If ws.[a2].Value = "" Then
Me.srID.Text = "SR-" & 1
Else
With ws.Cells(iRow, 1)
    Me.srID.Text = Left(.Value, 3) & CInt(Right(.Value, Len(.Value) - 3)) + 1
    End With
  End If
End Sub

In the userform1 there is a checkbox "zeefTest", if this is checked a 2nd userform "Fracties" will open. This userform contains more checkboxes and a select commandbutton. When the select commandbutton is checked the data will be put in worksheet("Result_Particles"). In this worksheet column "A" is also the unique ID. But this is dependent on the zeeftest checkbox being checked. The code for calling userform2 "Fracties" is :

Private Sub zeefTest_Click()
Fracties.Show

Dim sRow As Long

With Worksheets("Result_Particles").Range("A1")

sRow = Worksheets("Result_Particles").Range("A1").CurrentRegion.Rows.Count

.Offset(sRow, 0).Value = Me.srID.Value
End With

End Sub

Now when 2nd userform select commandbutton is clicked the userform unloads and the 1st userform save button can be clicked.

The problem I am having is that the userform1 code does not work correctly with code in userform2. I think it overrides the userform2 code when I click on the Save commandbutton in userform 1. The unique ID code is put in the worksheet("Result_Particles") properly, but the checkbox interior.colorindex does not write to the proper columns. It only does it correctly for the first row. The code for userform2 is:

Private Sub CmB1_Click()
Dim iRow As Long
With Worksheets("Result_Particles").Range("A1")
iRow = Worksheets("Result_Particles").Range("A1").CurrentRegion.Rows.Count
If Cbx1.Value = True Then
     .Offset(iRow, 1).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 1).Interior.ColorIndex = 15
End If

If Cbx2.Value = True Then
     .Offset(iRow, 2).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 2).Interior.ColorIndex = 15
End If

If Cbx3.Value = True Then
     .Offset(iRow, 3).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 3).Interior.ColorIndex = 15
End If

If Cbx4.Value = True Then
     .Offset(iRow, 4).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 4).Interior.ColorIndex = 15
End If
If Cbx5.Value = True Then
     .Offset(iRow, 5).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 5).Interior.ColorIndex = 15
End If

If Cbx6.Value = True Then
     .Offset(iRow, 6).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 6).Interior.ColorIndex = 15
End If

If Cbx7.Value = True Then
     .Offset(iRow, 7).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 7).Interior.ColorIndex = 15
End If

If Cbx8.Value = True Then
     .Offset(iRow, 8).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 8).Interior.ColorIndex = 15
End If

If Cbx9.Value = True Then
     .Offset(iRow, 9).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 9).Interior.ColorIndex = 15
End If

If Cbx10.Value = True Then
     .Offset(iRow, 10).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 10).Interior.ColorIndex = 15
End If

If Cbx11.Value = True Then
     .Offset(iRow, 11).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 11).Interior.ColorIndex = 15
End If

If Cbx12.Value = True Then
     .Offset(iRow, 12).Interior.ColorIndex = 2
    Else:
  .Offset(iRow, 12).Interior.ColorIndex = 15
End If
End With
Unload Me

End Sub

I hope someone can help me fix my code to work properly. I know this is not the neatest code so if anyone also knows a beter way to write the code for my checkboxes, this would also be much appreciated.

Thank you for your time.

You say: "the problem is that the userform1 code does not work correctly with code in userform2". Can you post the code from userform1 that does not work correctly? — dimitris
Also what do you mean by "does not work correctly"? What would you like it to do and is not done? — dimitris
The code in userform1 contains the userform_initialize and the zeefTest_click, they are both private subs that I presume only work for that userform. Now I want the unique ID to be placed in worksheets("srData") and in worksheet ("Result_Particles") the last only if checkbox zeefTest is True. This is not the problem. But when I then select the checkboxes in userform2 that has it's own private code, it does not offset the interior.colorindex in the correct columns. It seem to over write them. It work for the first row, but every other row will offset the same as the first row — Bellandra
I tried putting the code: .offset(iRow,0).Value=Me.srID.value in the private sub CmB1_click but then I get an error on the Me.srID part. As this code is not in this userform — Bellandra
You can refer to userform2 and its own objects from userform1 code if: 1) it is not unloaded but hidden, 2) you remove the keyword Private and 3) replace the keyword Me with Userform2 or with userform2's name if you have renamed it. .offset(iRow,0).Value = Userform2.srID.value — dimitris

dimitris dimitris · Accepted Answer · 2019-04-12T12:01:27

The result of this line:

Worksheets("Result_Particles").Range("A1").CurrentRegion.Rows.Count

is 1.

This is because CurrentRegion is applied to Range("A1") only, whose Rows is just one.

As far as I understand your goal, you should change that range to something broader, so that Count results to something more meaningful.

Let me know if this doesn't help.

With 2 userforms, having code that does not interact between the two

1 Answers