Userforms in Excel

M

Mali

hi - I have a fairly coded excel tool which has a number of userforms that
are both used as input and output to the end user......as the tool is
developing.....i find that in some cases not all my backend code
.....especially userform initialize is running.....for example if i have 7 do
while code in my userform initialize sub only the first three seem to
execute...there is nothing worng interms of my code......as such the 3 do
while statemetns are the same only the search creiteria is different....

should i do somethign to make my code run more efficiently????
 
M

Mike

Post your code with the 7 Do While loops and maybe we might spot something
your not seeing
 
M

Mali

hi mike .....here's the code....

Private Sub UserForm_Initialize()

Dim lRow As Long
Dim DB As Worksheet
Dim AM As Worksheet
Dim RowCount As Long
Dim AccountName As String
Dim C1 As String
Dim C2 As String
Dim C3 As String
Dim C4 As String
Dim C5 As String
Dim C6 As String
Dim C7 As String
Dim C8 As String
Dim C9 As String
Dim C10 As String
Set DB = Worksheets("DB")
Set AM = Worksheets("AM Input Sheet")
'#######################Load data from DB##################
AccountName = AM.Range("D44")
Me.Acc_Name.Value = AccountName
RowCount = 2
C1 = Me.C1.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C1 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C1A.Value = .Cells(RowCount, 7).Value
Me.C1B.Value = .Cells(RowCount, 8).Value
Me.C1C.Value = .Cells(RowCount, 9).Value

End If
End With
C2 = Me.Q2.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C2 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C2A.Value = .Cells(RowCount, 7).Value
Me.C2B.Value = .Cells(RowCount, 8).Value
Me.C2C.Value = .Cells(RowCount, 9).Value
End If
End With
C3 = Me.C3.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C3 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C3A.Value = .Cells(RowCount, 7).Value
Me.C3B.Value = .Cells(RowCount, 8).Value
Me.C3C.Value = .Cells(RowCount, 9).Value
End If
End With
C4 = Me.C4.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C4 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C4A.Value = .Cells(RowCount, 7).Value
Me.C4B.Value = .Cells(RowCount, 8).Value
Me.C4C.Value = .Cells(RowCount, 9).Value
End If
End With
C5 = Me.C5.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C5 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C5A.Value = .Cells(RowCount, 7).Value
Me.C5B.Value = .Cells(RowCount, 8).Value
Me.C5C.Value = .Cells(RowCount, 9).Value
End If
End With
C6 = Me.C6.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C6 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C6A.Value = .Cells(RowCount, 7).Value
Me.C6B.Value = .Cells(RowCount, 8).Value
Me.C6C.Value = .Cells(RowCount, 9).Value

End If
End With
C7 = Me.C7.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C7 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C7A.Value = .Cells(RowCount, 7).Value
Me.C7B.Value = .Cells(RowCount, 8).Value
Me.C7C.Value = .Cells(RowCount, 9).Value
End If
End With
C8 = Me.C8.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C8 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C8A.Value = .Cells(RowCount, 7).Value
Me.C8B.Value = .Cells(RowCount, 8).Value
Me.C8C.Value = .Cells(RowCount, 9).Value
End If
End With
C9 = Me.C9.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C9 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C9A.Value = .Cells(RowCount, 7).Value
Me.C9B.Value = .Cells(RowCount, 8).Value
Me.C9C.Value = .Cells(RowCount, 9).Value
End If
End With
C10 = Me.C10.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C10 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C10A.Value = .Cells(RowCount, 7).Value
Me.C10B.Value = .Cells(RowCount, 8).Value
Me.C10C.Value = .Cells(RowCount, 9).Value
End If
End With
C11 = Me.C11.Value
With Sheets("DB")
found = False
Do While .Range("B" & RowCount) <> ""
If AccountName = DB.Range("B" & RowCount) And _
C11 = DB.Range("D" & RowCount) Then
found = True
Exit Do
End If
RowCount = RowCount + 1

Loop
If found = True Then
Me.C11A.Value = .Cells(RowCount, 7).Value
Me.C11B.Value = .Cells(RowCount, 8).Value
Me.C11C.Value = .Cells(RowCount, 9).Value
End If
End With


'####################format text boxes##########################
Me.C1A.Value = Format(Me.C1A.Value, "#,##0")
Me.C2A.Value = Format(Me.C2A.Value, "#,##0")
Me.C3A.Value = Format(Me.C3A.Value, "#,##0")
Me.C4A.Value = Format(Me.C4A.Value, "#,##0")
Me.C5A.Value = Format(Me.C5A.Value, "#,##0")
Me.C6A.Value = Format(Me.C6A.Value, "#,##0")
Me.C7A.Value = Format(Me.C7A.Value, "#,##0")
Me.C8A.Value = Format(Me.C8A.Value, "#,##0")
Me.C9A.Value = Format(Me.C9A.Value, "#,##0")
Me.C10A.Value = Format(Me.C10A.Value, "#,##0")
Me.C11A.Value = Format(Me.C11A.Value, "#,##0")
Me.TA.Value = Format(Me.TA.Value, "#,##0")
Me.C1B.Value = Format(Me.C1B.Value, "#,##0")
Me.C2B.Value = Format(Me.C2B.Value, "#,##0")
Me.C3B.Value = Format(Me.C3B.Value, "#,##0")
Me.C4B.Value = Format(Me.C4B.Value, "#,##0")
Me.C5B.Value = Format(Me.C5B.Value, "#,##0")
Me.C6B.Value = Format(Me.C6B.Value, "#,##0")
Me.C7B.Value = Format(Me.C7B.Value, "#,##0")
Me.C8B.Value = Format(Me.C8B.Value, "#,##0")
Me.C9B.Value = Format(Me.C9B.Value, "#,##0")
Me.C10B.Value = Format(Me.C10B.Value, "#,##0")
Me.C11B.Value = Format(Me.C11B.Value, "#,##0")
Me.TB.Value = Format(Me.TB.Value, "#,##0")
Me.C1C.Value = Format(Me.C1C.Value, "#,##0")
Me.C2C.Value = Format(Me.C2C.Value, "#,##0")
Me.C3C.Value = Format(Me.C3C.Value, "#,##0")
Me.C4C.Value = Format(Me.C4C.Value, "#,##0")
Me.C5C.Value = Format(Me.C5C.Value, "#,##0")
Me.C6C.Value = Format(Me.C6C.Value, "#,##0")
Me.C7C.Value = Format(Me.C7C.Value, "#,##0")
Me.C8C.Value = Format(Me.C8C.Value, "#,##0")
Me.C9C.Value = Format(Me.C9C.Value, "#,##0")
Me.QC10C.Value = Format(Me.C10C.Value, "#,##0")
Me.C11C.Value = Format(Me.C11C.Value, "#,##0")
Me.TC.Value = Format(Me.TC.Value, "#,##0")

End Sub


this is a lot of code..but the key point is that only the first 3 do while
seem to execute.......thx a lot for any help....i'm really strugglign to find
out why this isnt working...
 
M

Mali

hi Mike - found the answer...i needed to declare the rowcount within every
while.....thx for u'r reply though.!!!!!!!!!!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assign range to userform labels and textboxes 0
Calling Stack size in Userforms 0
Tagging Misspell Words with "##" 0
Excel Userform 3
Display one sheet or two userforms while code is vba code is runningon another 2
Disable all Control Events on a Userform when it is Intialized 1
Errors Closing Userform 0
Position Userform In Cells Portion of Excel Only 1

Top