Hidden sheet bleeds through

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

Hi,
I have a workbook that is about 5.5MB and I'm using a hidden sheet
(sheet7) to populate a userform. The user selects search criteria from
combo boxes on the first userform and then may get a second userform
if the first userform returns too many records. The problem is that
when the first userform is launched (from a worksheet button) the
column on the hidden sheet that contains the employee list "bleeds"
through to the current sheet. A secondary problem is that the userform
doesn't unload. Can anyone give me an idea as to what is happening
here? The initialization code for each form is listed below. Thanks.
James

Private Sub UserForm_Initialize() 'Primary userform
Dim R As Long
CBox3.Enabled = False
CBox4.Enabled = False
Sheets(7).Range("A2:O2").ClearContents
LastRow = Sheets(2).Cells(Rows.Count, 5).End(xlUp).Row
Raw_Data
ChkBx.Value = False
ChkBx_Change
CmdBtn.Enabled = True
For R = 2 To Worksheets(7).Cells(2, 19).End(xlDown).Row
If Worksheets(7).Cells(R, 19).Value <> "" Then
CBox1.AddItem Worksheets(7).Cells(R, 19).Value
End If
Next
For R = 2 To Worksheets(7).Cells(2, 17).End(xlDown).Row
If Worksheets(7).Cells(R, 17).Value <> "" Then
CBox3.AddItem Worksheets(7).Cells(R, 17).Value
End If
Next
For R = 2 To Worksheets(7).Cells(2, 18).End(xlDown).Row
If Worksheets(7).Cells(R, 18).Value <> "" Then
CBox4.AddItem Worksheets(7).Cells(R, 18).Value
End If
Next
ChkBx.Caption = "Print all student records for " & _
Sheets(1).Cells(8, 2).Value & "."
End Sub



Private Sub UserForm_Initialize() 'Secondary userform and command
button click code
Application.EnableEvents = True
TextBox1.Text = "You are about to print " & (LastRow - 3) & " records.
"
End Sub
Private Sub CommandButton1_Click()
On Error Resume Next
Secondary.Hide
Unload Primary
Select Case True
Case OptBtn1
AllRecordsPrint
Case OptBtn2
If Worksheets(Sheets.Count).Name <> "Summary" Then
Add_Again
End If
Summary
AddCBX
Case OptBtn3
DoEvents
Unload Secondary
Primary.Show
Turn_Off
Case Else
End Select
Unload Secondary
DoEvents
End Sub
 
Just a few suggestions

In the userform try using Me.Repaint

In the workbook if you have Application.ScreenUpdating set to False you may need to re enable it whilst the other processing is done

Just quick observations from me which may help :)
 
Just a thought...
Might be a memory issue!
Have you tried using one userform with 2 frame controls that you toggle
their visibility to work like separate pages? You can have the controls
on 1st userform in Frame1 (page1) and controls on 2nd userform in
Frame2 (page2). This would also eliminate any 'cross-talk' issues
between the 2 userforms.
 
Just a thought...
Might be a memory issue!
Have you tried using one userform with 2 frame controls that you toggle
their visibility to work like separate pages? You can have the controls
on 1st userform in Frame1 (page1) and controls on 2nd userform in
Frame2 (page2). This would also eliminate any 'cross-talk' issues
between the 2 userforms.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thank you, Gentlemen, I really appreciate your help. I got it to work
by giving XL something else to between the unload and load processes
by having it select the sheet that it was already on. I did turn off
screen updating and used DoEvents, but those didn't work until I used
the sheet select.
James
 
Back
Top