You was asking how having 3 userforms, all populated by the same controls, running the same event macro; my implicit suggestion was “use a single userform and there is no any problem to be solved”
Your detailed description of your environment and the overall objectives doesn’t say that using a single userform is not feasible. So I stay with my suggestion of using only one single userform.
I understand the data to be manipultaed lies on the activesheet, so dealing with them should be not a problem; anyway, if you need you may set different scope based on which commandbutton initiated the userform.
Hi and thank's for your reply.
I'm sorry if I didn't made me clear enough in the beginning, the 2 sheets that i presented are pretty equal, I use a common UF for thoose,
Why I mentioned them, was that they where the next ones to solve
I have made code for the member table, and if I do this straight forward, I have to duplicate the code for AccountDoc with smal modifications,
They do the same operations on a different table.
The thing is that I have code to manipulate member table, and there is no secret in that, I can present it, the problem is its not written in English, i have to translate.
OnAddMember_Click()
UF_Member.Tag = 1
UF_Member.Show
End sub
OnChangeMember_Click()
UF_Member.Tag = 2
UF_Member.Show
End sub
OnDeleteMembes_Click()
UF_Member.Tag = 3
UF_Member.Show
End sub
'UF_Member code class:
Private Sub UserForm_Activate()
If Me.Tag = 1 Then
Call AddNewMember
ElseIf Me.Tag = 2 Then
Call ChangeNewMember
ElseIf Me.Tag = 3 Then
Call DeleteNewMember
Sub SetupAddMember()
Me.Caption="Create member"
...
Me.btnAddMember.Visible = True
End Sub
Sub SetupChangeMember()
Me.Caption="Change member"
...
Me.btnChangeMember.Visible = True
End Sub
Sub SetupDeleteMember()
Me.Caption="Delete member"
...
Me.btnDeleteMember.Visible = True
End Sub
Private Sub btnAddMember_Click()
Dim arr() As Variant
...
arr = StoreUFInArray()
Call AddMember(arr)
Unload Me
End Sub
Private Sub btnChangeMember_Click()
Dim arr() As Variant
...
arr = StoreUFInArray()
Call ChangeMember(CLng(Me.tb_LineInTbl), arr)
Unload Me
End Sub
Private Sub btnDeleteMember_Click()
Dim arr() As Variant
...
arr = StoreUFInArray()
Call DeleteMember(arr)
Unload Me
End Sub
So far I have decided that te UF_ code should not have any code related to the worksheet
I have isolated the code to a modAction module
The other thing is that the process to transfer the data from UF_ to sheet is done by sending an array
But if you use an event, the data had to use another way to get the data from userform
The code below is present like it is now, for 1 table in one sheet. Has to be modified .
modAction:
Public Sub AddMember(arr() As Variant)
Dim appstate As CAppState
Err.Clear
On Error GoTo ErrorHandling
With Sheet4
'turn of events and GUI updates
Set appstate = New CAppState
appstate.SetState None
With .ListObject(1)
'At start the table has a empty row, do not add then
If CLng(arr(1)) > 1 Then
.ListRows.Add AlwaysInsert:=True
End If
Call AddArrayToTableRow(.DataBodyRange.Rows.Count, arr())
Range("MedlNr").value = arr(1)
End With
Cleanup:
Set appstate = Nothing
End With
Exit Sub
ErrorHandling:
...
End Sub
Public Sub ChangeMember(row as Long, arr() As Variant)
Dim appstate As CAppState
Err.Clear
On Error GoTo ErrorHandling
With Sheet4
'turn of events and GUI updates
Set appstate = New CAppState
appstate.SetState None
With .ListObject(1)
if row > HeaderRowRange.Row and row < .HeaderRow.Rows.Count Then
Call AddArrayToTableRow(row, arr())
Endif
End With
...
End Sub
Sub AddArrayToTableRow(rad_nr As Long, arr() As Variant)
Dim Tag As Integer
Dim appstate As CAppState
Err.Clear
On Error GoTo ErrorHandling
With Sheet4
Set appstate = New CAppState
appstate.SetState None
With .ListObject(1)
If IsNumeric(arr(0)) Then
Tag = CInt(arr(0))
Else
MsgBox "Wrong Format on sent Message"
Exit Sub
End If
If Tag = 1 Then
If rad_nr = .Rows.Count Then
.Cells(rad_nr, 1).value = arr(1)
.Cells(rad_nr, 2).value = Format(CDate(arr(2)), "dd/mm/yyyy")
.Cells(rad_nr, 3).value = arr(3)
.Cells(rad_nr, 4).value = arr(4)
.Cells(rad_nr, 5).value = arr(5)
.Cells(rad_nr, 6).value = arr(6)
End If
ElseIf Tag = 2 Then
If rad_nr > 0 And rad_nr <= .Rows.Count Then
'Member num and date do not change
'Fill all fields even if its not changed
.Cells(rad_nr, 3).value = arr(3)
.Cells(rad_nr, 4).value = arr(4)
.Cells(rad_nr, 5).value = arr(5)
.Cells(rad_nr, 6).value = arr(6)
'Kontigent legges til ved Tag = 3
End If
ElseIf Tag = 3 Then
MsgBox("Wrong logic should newer end up here")
Else
MsgBox ("Member dialog wrong configurated")
End If
End With
-----
If I understand you, you think to use the events in each user form and call the
AddMember, ChangeMember and DeleteMember from UF without using events
Theese functions has to be made more general
It might be difficult to make connection between an CButton class and the UF's
I've seen somewhere to use a callback to register your class/UF at the other side of the connection
When I sad 3 UserForms. I thought in general termes, there are differences between Members and AccountDoc
I was looking for a general way to do it, and was trying to learn more about events,
It might be that this wasn't right way to solve it this time, I'm not an expert, just looking for answers
Agen I'm sorry for the inconvenience, but I'm always focusing positive and think positive
even if you had some comments to my so far added information
Thanks for now, I'll be answering a respons on monday
Jo-Helge
)
