Why is this chunk of code freezing

[pigeonmilk]

My other thread got deleted. Here is my problem again, the code seems to freeze at the with end statements. If someone could help me I can send them my file. Its a template to enter findings.

It doesnt run with the commandbutton but when i run it through the debugger it works fine. :S

'Start and Load Userform. I tried to use Userform1.show on commandbutton1_click and it still freezes


Private Sub CommandButton1_Click()
Call Module2.Start
End Sub



Initial Start Sub

Public Sub Start()
Load UserForm2
' Initalizations
FLAG = True
Ccount = 0
ActCycle = 1
cycleflag = False


Dim dcell As Range
Dim this As Workbook
Dim ws3 As Worksheet
Dim Mystr As String
Dim select1 As Integer
Dim drows As Long

Set this = ActiveWorkbook
Set ws3 = this.Worksheets("Setup")

If this.Worksheets("FindingsData").Range("B5") <> "" Then
select1 = this.Worksheets("FindingsData").Range("B5")
Else
UserForm3.Show
select1 = this.Worksheets("FindingsData").Range("B5")
End If
If select1 = 111 Then
UserForm2.MultiPage1.Pages("Page2").Enabled = True
UserForm2.MultiPage1.Pages("Page1").Enabled = False
Else
UserForm2.MultiPage1.Pages("Page2").Enabled = False
UserForm2.MultiPage1.Pages("Page1").Enabled = True
End If

'Fill in the comboboxes
drows = ws3.Range("A2", ws3.Range("A2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("A2", "A" & drows + 1)
Mystr = dcell
UserForm2.ComboBox1.AddItem (Mystr)
Next dcell
drows = ws3.Range("C2", ws3.Range("C2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("C2", "C" & drows + 1)
Mystr = dcell
UserForm2.ComboBox2.AddItem (Mystr)
Next dcell
drows = ws3.Range("E2", ws3.Range("E2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("E2", "E" & drows + 1)
Mystr = dcell
UserForm2.ComboBox3.AddItem (Mystr)
Next dcell
drows = ws3.Range("G2", ws3.Range("G2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("G2", "G" & drows + 1)
Mystr = dcell
UserForm2.ComboBox4.AddItem (Mystr)
Next dcell
drows = ws3.Range("I2", ws3.Range("I2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("I2", "I" & drows + 1)
Mystr = dcell
UserForm2.ComboBox5.AddItem (Mystr)
Next dcell
drows = ws3.Range("T2", ws3.Range("T2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("T2", "T" & drows + 1)
Mystr = dcell
UserForm2.ComboBox6.AddItem (Mystr)
Next dcell
drows = ws3.Range("K2", ws3.Range("K2").End(xlDown)).Rows.Count
For Each dcell In ws3.Range("K2", "K" & drows + 1)
Mystr = dcell
UserForm2.ComboBox7.AddItem (Mystr)
Next dcell


'Count number of cycles
cycleC = this.Worksheets("ToDatabase").[A65536].End(xlUp).Row
MsgBox cycleC

If cycleC <> 1 Then
Call Module1.LoadData
Else 'New Cycle counter ( Actcycle = 1)
MsgBox "im here"
cycleC = 2
End If
UserForm2.Show

End Sub




Load Data Sub

Public Sub LoadData()
Dim this As Workbook
Set this = ActiveWorkbook

Dim Ccountcounter As Integer
Dim i As Integer
Dim q As Integer
Dim addcount As Integer
Dim zz As Integer, qq As Integer

UserForm2.TextBox1.Enabled = True
UserForm2.TextBox2.Enabled = True
UserForm2.ComboBox1.Enabled = True
UserForm2.ComboBox2.Enabled = True
UserForm2.ComboBox3.Enabled = True
UserForm2.ComboBox4.Enabled = True
UserForm2.ComboBox6.Enabled = True

MsgBox "im here 1"
Worksheets("ToDatabase").Visible = True
Worksheets("FindingsData").Visible = True
With this.Worksheets("ToDatabase")
' If saved template is opened, fill in with data that is stored in last cycle
UserForm2.TextBox1.value = .Range("A" & cycleC)
UserForm2.TextBox2.value = .Range("B" & cycleC)
UserForm2.ComboBox1.value = .Range("D" & cycleC)
UserForm2.ComboBox2.value = .Range("C" & cycleC)
UserForm2.ComboBox3.value = .Range("F" & cycleC)
UserForm2.ComboBox4.value = .Range("G" & cycleC)
UserForm2.ComboBox5.value = .Range("E" & cycleC)
UserForm2.ComboBox6.value = .Range("AV" & cycleC)
UserForm2.TextBox3.value = .Range("V" & cycleC)
UserForm2.TextBox4.value = .Range("W" & cycleC)
UserForm2.TextBox5.value = .Range("H" & cycleC)
UserForm2.ComboBox7.value = .Range("S" & cycleC)
UserForm2.TextBox6.value = .Range("AS" & cycleC)
UserForm2.Label123.Caption = .Range("L" & cycleC)
UserForm2.TextBox113.value = .Range("T" & cycleC)
ActCycle = .Range("L" & cycleC)
UserForm2.TextBox1.Enabled = False
UserForm2.TextBox2.Enabled = False
UserForm2.ComboBox1.Enabled = False
UserForm2.ComboBox2.Enabled = False
UserForm2.ComboBox3.Enabled = False
UserForm2.ComboBox4.Enabled = False
UserForm2.ComboBox6.Enabled = False
End With
MsgBox "im here2"
' Get previous Oringinal Findings data if any
If Worksheets("FindingsData").Range("A1") <> "" Then
Ccountcounter = Worksheets("FindingsData").Range("A1")
For i = 0 To Ccountcounter - 1
Call UserForm2.CommandButton7_Click 'Load ccountcounter of controls to load info with
Next
For i = 1 To Ccountcounter 'Load the data for each control
UserForm2.MultiPage1.Pages(1).Controls("cmdCC" & i).value = this.Worksheets("FindingsData").Range("C" & i)
UserForm2.MultiPage1.Pages(1).Controls("FA" & i).value = this.Worksheets("FindingsData").Range("D" & i)
UserForm2.MultiPage1.Pages(1).Controls("MM" & i).value = this.Worksheets("FindingsData").Range("E" & i)
UserForm2.MultiPage1.Pages(1).Controls("Types" & i).value = this.Worksheets("FindingsData").Range("F" & i)
UserForm2.MultiPage1.Pages(1).Controls("lblCycleC" & i).Caption = this.Worksheets("FindingsData").Range("G" & i)
UserForm2.MultiPage1.Pages(1).Controls("cmdo" & i).value = this.Worksheets("FindingsData").Range("H" & i)
UserForm2.MultiPage1.Pages(1).Controls("cmbo" & i).value = this.Worksheets("FindingsData").Range("I" & i)
Next
End If
MsgBox "im here3"
'Fill in data for the ManualFindings Tab
addcount = 0
qq = 0
zz = 85
For zz = 85 To 112
qq = qq + 1
If qq <> 23 Then
UserForm2.Controls("TextBox" & zz).value = this.Worksheets("FindingsData").Range("M" & qq)
If this.Worksheets("FindingsData").Range("M" & qq) = 0 Then
UserForm2.Controls("TextBox" & zz).value = ""
End If
End If
Next
MsgBox "im here4"
' Count the number of addressed findings for the OriginalFindings Tab
For i = 1 To Ccountcounter
With this.Worksheets("FindingsData")
If .Range("I" & i) = "Finding is NA" Or .Range("I" & i) = "Addressed" Or _
.Range("I" & i) = "In next release" Then
addcount = addcount + 1
End If
End With
Next
MsgBox "im here5"
'Loads Information bar with data if any
With this.Worksheets("FindingsData")
UserForm2.Label21.Caption = addcount + CInt(.Range("M" & 25) + .Range("M" & 26) + .Range("M" & 27))
UserForm2.Label20.Caption = Ccount + CInt(.Range("M" & 1))
UserForm2.Label18.Caption = CInt(this.Worksheets("ToDatabase").Range("M" & cycleC))
UserForm2.Label22.Caption = CInt(this.Worksheets("ToDatabase").Range("N" & cycleC))
UserForm2.Label19.Caption = CInt(this.Worksheets("ToDatabase").Range("P" & cycleC))
UserForm2.Label23.Caption = CInt(this.Worksheets("ToDatabase").Range("Q" & cycleC))
If .Range("B" & 2) Then 'Show Checklist if opened before
Call UserForm2.CommandButton5_Click
End If
End With
MsgBox "im here6"
End Sub



please help thanks

[Simon Lloyd]

You don't declare cycleC or ActCycle, you need to use option explicit at the top of your modules that way it will flag up naming problems!

[pigeonmilk]

sorry this is not the full code,

I do have those variable declared as public because the userform needs it.

I have narrowed down the problem to this

If I comment out these two parts out , my program works. I think it has to do with the labels, what format does it take string, int or anything? What happens if it reads in a empty cell?

UserForm2.Label123.Caption = .Range("L" & cycleC)
UserForm2.TextBox113.value = .Range("T" & cycleC)
ActCycle = .Range("L" & cycleC)

and

With this.Worksheets("FindingsData")
UserForm2.Label21.Caption = addcount + CInt(.Range("M" & 25) + .Range("M" & 26) + .Range("M" & 27))
UserForm2.Label20.Caption = Ccount + CInt(.Range("M" & 1))
UserForm2.Label18.Caption = CInt(this.Worksheets("ToDatabase").Range("M" & cycleC))
UserForm2.Label22.Caption = CInt(this.Worksheets("ToDatabase").Range("N" & cycleC))
UserForm2.Label19.Caption = CInt(this.Worksheets("ToDatabase").Range("P" & cycleC))
UserForm2.Label23.Caption = CInt(this.Worksheets("ToDatabase").Range("Q" & cycleC))
If .Range("B" & 2) Then 'Show Checklist if opened before
Call UserForm2.CommandButton5_Click
End If
End With

I have never encountered such a proble before

[Simon Lloyd]

you could try the extension .Value or .Text after each, what do you have them declared as?

[pigeonmilk]

Thanks for the reply Simon, I tried what you said and it didnt make a difference.

Some of them are strings some is integers.


Is there a difference between,

UserForm2.Label123.Caption = .Range("L" & cycleC)

and UserForm2.Label123.Caption = .Cells(cycleC,12) ?

[Simon Lloyd]

No, as long as you have used the qualifier (.) after a with Sheets(....)statement, have you checked to see that cyclec is actually returning an integer? by the way best if you DIM it as Long, try adding a message box at the top of your code after the Dim's like this:

MsgBox "cycleC = " & cyclec & " - " & "ActCycle = " & ActCycle
this way you will see what value you are passing to the code!