I have a fairly complex UserFrom called UF0_QCP and need to save it's data in
the workbook for which it was used (so that I can reinitialise later with the
last-used data. I have tried the following pair of subs but no msgbox from
either. What am I missing please (don't say the whole concept!)? Regards,
Brett


Private Sub UF0_QCP_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox "QueryClose"
If CloseMode < 2 Then
Dim rw As Integer, cl As Integer
rw = Range("QCP.memory").Row + 1: cl = Range("QCP.memory").Column
For Each Control In UF0_QCP.Controls
On Error Resume Next
With Sheets("LAUNCHPAD")
.Cells(rw, cl) = Control.Name: .Cells(rw, cl + 2) =
Control.Caption: .Cells(rw, cl + 4) = Control.Value
.Cells(rw, cl + 6) = Control.Enabled: .Cells(rw, cl + 7) =
Control.Visible
.Cells(rw, cl + 8) = Control.Top: .Cells(rw, cl + 9) =
Control.Left
End With
rw = rw + 1: On Error GoTo 0
Next Control
Sheets("LAUNCHPAD").Range("QCP.memory") = True
End If
End Sub

AND FOR INITIALIZING;
Private Sub UF0_QCP_Initialize()
MsgBox "Initialize"
If Sheets("LAUNCHPAD").Range("QCP.memory") = True Then
Dim rw As Integer, cl As Integer
rw = Sheets("LAUNCHPAD").Range("QCP.memory").Row + 1: cl =
Sheets("LAUNCHPAD").Range("QCP.memory").Column
For Each Control In UF0_QCP.Controls
On Error Resume Next
With Sheets("LAUNCHPAD")
Control.Name = .Cells(rw, cl): Control.Caption =
..Cells(rw, cl + 2): Control.Value = .Cells(rw, cl + 4)
Control.Enabled = .Cells(rw, cl + 6): Control.Visible =
..Cells(rw, cl + 7)
Control.Top = .Cells(rw, cl + 8): Control.Left =
..Cells(rw, cl + 9)
End With
rw = rw + 1: On Error GoTo 0
Next Control
Sheets("LAUNCHPAD").Range("QCP.memory") = False
End If
End Sub

Hi Brett,

Try nameing the subs as follows:-

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Assuming that you have the code in the userform code module, you don't use
the userform name.

The only events you can have are those that get created when you select the
userform from the dropdown at the top left of the VBA editor and then select
the event name from the top right dropdown. The subs then get created with
their correct name.

--
Regards,

OssieMac


"Brett" wrote:

I have a fairly complex UserFrom called UF0_QCP and need to save it's data in
the workbook for which it was used (so that I can reinitialise later with the
last-used data. I have tried the following pair of subs but no msgbox from
either. What am I missing please (don't say the whole concept!)? Regards,
Brett


Private Sub UF0_QCP_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox "QueryClose"
If CloseMode < 2 Then
Dim rw As Integer, cl As Integer
rw = Range("QCP.memory").Row + 1: cl = Range("QCP.memory").Column
For Each Control In UF0_QCP.Controls
On Error Resume Next
With Sheets("LAUNCHPAD")
.Cells(rw, cl) = Control.Name: .Cells(rw, cl + 2) =
Control.Caption: .Cells(rw, cl + 4) = Control.Value
.Cells(rw, cl + 6) = Control.Enabled: .Cells(rw, cl + 7) =
Control.Visible
.Cells(rw, cl + 8) = Control.Top: .Cells(rw, cl + 9) =
Control.Left
End With
rw = rw + 1: On Error GoTo 0
Next Control
Sheets("LAUNCHPAD").Range("QCP.memory") = True
End If
End Sub

AND FOR INITIALIZING;
Private Sub UF0_QCP_Initialize()
MsgBox "Initialize"
If Sheets("LAUNCHPAD").Range("QCP.memory") = True Then
Dim rw As Integer, cl As Integer
rw = Sheets("LAUNCHPAD").Range("QCP.memory").Row + 1: cl =
Sheets("LAUNCHPAD").Range("QCP.memory").Column
For Each Control In UF0_QCP.Controls
On Error Resume Next
With Sheets("LAUNCHPAD")
Control.Name = .Cells(rw, cl): Control.Caption =
.Cells(rw, cl + 2): Control.Value = .Cells(rw, cl + 4)
Control.Enabled = .Cells(rw, cl + 6): Control.Visible =
.Cells(rw, cl + 7)
Control.Top = .Cells(rw, cl + 8): Control.Left =
.Cells(rw, cl + 9)
End With
rw = rw + 1: On Error GoTo 0
Next Control
Sheets("LAUNCHPAD").Range("QCP.memory") = False
End If
End Sub

Hi Ossie, nice time of year eh? You know there are times when I'm far to
clever for my own good. I had actually done exactly as you described, but
thinking that I'm half smart I changed the USERFORM to UF0_QCP in the
procedure names (because that's what you do with these things, right?).
********. It works perfectly now. Thanks for the tip. Brett

"OssieMac" wrote:

Hi Brett,

Try nameing the subs as follows:-

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Assuming that you have the code in the userform code module, you don't use
the userform name.

The only events you can have are those that get created when you select the
userform from the dropdown at the top left of the VBA editor and then select
the event name from the top right dropdown. The subs then get created with
their correct name.

--
Regards,

OssieMac


"Brett" wrote:

I have a fairly complex UserFrom called UF0_QCP and need to save it's data in
the workbook for which it was used (so that I can reinitialise later with the
last-used data. I have tried the following pair of subs but no msgbox from
either. What am I missing please (don't say the whole concept!)? Regards,
Brett


Private Sub UF0_QCP_QueryClose(Cancel As Integer, CloseMode As Integer)
MsgBox "QueryClose"
If CloseMode < 2 Then
Dim rw As Integer, cl As Integer
rw = Range("QCP.memory").Row + 1: cl = Range("QCP.memory").Column
For Each Control In UF0_QCP.Controls
On Error Resume Next
With Sheets("LAUNCHPAD")
.Cells(rw, cl) = Control.Name: .Cells(rw, cl + 2) =
Control.Caption: .Cells(rw, cl + 4) = Control.Value
.Cells(rw, cl + 6) = Control.Enabled: .Cells(rw, cl + 7) =
Control.Visible
.Cells(rw, cl + 8) = Control.Top: .Cells(rw, cl + 9) =
Control.Left
End With
rw = rw + 1: On Error GoTo 0
Next Control
Sheets("LAUNCHPAD").Range("QCP.memory") = True
End If
End Sub

AND FOR INITIALIZING;
Private Sub UF0_QCP_Initialize()
MsgBox "Initialize"
If Sheets("LAUNCHPAD").Range("QCP.memory") = True Then
Dim rw As Integer, cl As Integer
rw = Sheets("LAUNCHPAD").Range("QCP.memory").Row + 1: cl =
Sheets("LAUNCHPAD").Range("QCP.memory").Column
For Each Control In UF0_QCP.Controls
On Error Resume Next
With Sheets("LAUNCHPAD")
Control.Name = .Cells(rw, cl): Control.Caption =
.Cells(rw, cl + 2): Control.Value = .Cells(rw, cl + 4)
Control.Enabled = .Cells(rw, cl + 6): Control.Visible =
.Cells(rw, cl + 7)
Control.Top = .Cells(rw, cl + 8): Control.Left =
.Cells(rw, cl + 9)
End With
rw = rw + 1: On Error GoTo 0
Next Control
Sheets("LAUNCHPAD").Range("QCP.memory") = False
End If
End Sub