pigeonmilk
07-16-2008, 06:18 AM
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
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