progress Bar

[mas70]

Hello All
I need your help i here by attaching the snap shot with present formula. row 7 contains date starting on column R as 6-6-2009. Now as my row 67 column L contains date & no date on column M . If i have got date in column L & m progress bar works perfect. Is it possible to modify this formula so that i want start of progress bar according to the date on column L.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 105px"><COL style="WIDTH: 110px"><COL style="WIDTH: 26px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>L</TD><TD>M</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">66</TD><TD style="COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">11-Jun-09</TD><TD style="COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">XXX</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">F</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; COLOR: #339933; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">XXX</TD><TD style="FONT-WEIGHT: bold; COLOR: #339933; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; COLOR: #339933; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; COLOR: #339933; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; COLOR: #339933; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">67</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: center">11-Jun-09</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: center">A</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">68</TD><TD style="COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">XXX</TD><TD style="COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">2-Jun-09</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center">P</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">69</TD><TD style="COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">XXX</TD><TD style="COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">20-Jun-09</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">F</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; COLOR: #339933; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #339933; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">70</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8"></TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: center">XXX</TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD><TD style="FONT-FAMILY: Arial Narrow; BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: center"></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L66</TD><TD>=J65</TD></TR><TR><TD>M66</TD><TD>=K65</TD></TR><TR><TD>R66</TD><TD>=IF(OR(AND(R$7>=$L66,R$7<=$M66),AND($L66<>0,R$7>=$L66,$M66=0,R$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>S66</TD><TD>=IF(OR(AND(S$7>=$L66,S$7<=$M66),AND($L66<>0,S$7>=$L66,$M66=0,S$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>T66</TD><TD>=IF(OR(AND(T$7>=$L66,T$7<=$M66),AND($L66<>0,T$7>=$L66,$M66=0,T$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>U66</TD><TD>=IF(OR(AND(U$7>=$L66,U$7<=$M66),AND($L66<>0,U$7>=$L66,$M66=0,U$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>V66</TD><TD>=IF(OR(AND(V$7>=$L66,V$7<=$M66),AND($L66<>0,V$7>=$L66,$M66=0,V$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>W66</TD><TD>=IF(OR(AND(W$7>=$L66,W$7<=$M66),AND($L66<>0,W$7>=$L66,$M66=0,W$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>X66</TD><TD>=IF(OR(AND(X$7>=$L66,X$7<=$M66),AND($L66<>0,X$7>=$L66,$M66=0,X$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>Y66</TD><TD>=IF(OR(AND(Y$7>=$L66,Y$7<=$M66),AND($L66<>0,Y$7>=$L66,$M66=0,Y$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>Z66</TD><TD>=IF(OR(AND(Z$7>=$L66,Z$7<=$M66),AND($L66<>0,Z$7>=$L66,$M66=0,Z$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>AA66</TD><TD>=IF(OR(AND(AA$7>=$L66,AA$7<=$M66),AND($L66<>0,AA$7>=$L66,$M66=0,AA$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>AB66</TD><TD>=IF(OR(AND(AB$7>=$L66,AB$7<=$M66),AND($L66<>0,AB$7>=$L66,$M66=0,AB$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>R67</TD><TD>=IF(OR(AND(R$7>=$L67,R$7<=$M67),AND($L67<>0,R$7>=$L67,$M67=0,R$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>S67</TD><TD>=IF(OR(AND(S$7>=$L67,S$7<=$M67),AND($L67<>0,S$7>=$L67,$M67=0,S$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>T67</TD><TD>=IF(OR(AND(T$7>=$L67,T$7<=$M67),AND($L67<>0,T$7>=$L67,$M67=0,T$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>U67</TD><TD>=IF(OR(AND(U$7>=$L67,U$7<=$M67),AND($L67<>0,U$7>=$L67,$M67=0,U$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>V67</TD><TD>=IF(OR(AND(V$7>=$L67,V$7<=$M67),AND($L67<>0,V$7>=$L67,$M67=0,V$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>W67</TD><TD>=IF(OR(AND(W$7>=$L67,W$7<=$M67),AND($L67<>0,W$7>=$L67,$M67=0,W$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>X67</TD><TD>=IF(OR(AND(X$7>=$L67,X$7<=$M67),AND($L67<>0,X$7>=$L67,$M67=0,X$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>Y67</TD><TD>=IF(OR(AND(Y$7>=$L67,Y$7<=$M67),AND($L67<>0,Y$7>=$L67,$M67=0,Y$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>Z67</TD><TD>=IF(OR(AND(Z$7>=$L67,Z$7<=$M67),AND($L67<>0,Z$7>=$L67,$M67=0,Z$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>AA67</TD><TD>=IF(OR(AND(AA$7>=$L67,AA$7<=$M67),AND($L67<>0,AA$7>=$L67,$M67=0,AA$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR><TR><TD>AB67</TD><TD>=IF(OR(AND(AB$7>=$L67,AB$7<=$M67),AND($L67<>0,AB$7>=$L67,$M67=0,AB$7<=TODAY()-MOD(TODAY(),7)-1)),1,0)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[jbeaucaire]

Can you manually mockup what you WANT to see without all those formulas, too. It would be helpful if your posted sample showed the info from row 7, too, not critical, but you could show that, yes?

I want to make sure it's clearly displayed what should be shown in the progress bar based on the various status of dates in L & M.

 

[mas70]

Can you manually mockup what you WANT to see without all those formulas, too. It would be helpful if your posted sample showed the info from row 7, too, not critical, but you could show that, yes?

I want to make sure it's clearly displayed what should be shown in the progress bar based on the various status of dates in L & M.

Thanks for your reply

following in row 7 information

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 24px"><COL style="WIDTH: 27px"><COL style="WIDTH: 24px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD><TD>AD</TD><TD>AE</TD></TR><TR style="HEIGHT: 16px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">06</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">07</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">08</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">19</TD></TR></TBODY></TABLE>
yes if date avaliable in column L i want on that perticular date hight light the bar
thanks in advance

 

[mas70]

Hello every one<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
One more point I need to point out one point is formula that I posted compare the value in column L & M if it is true value 1 show in cells from column R to AZ. if value 1 is true by conditional formatting color of cell ( i.e. Progress bar ). my question is I want your help to modify the formula if only column L contains a date from that date present date(in cell Ap3 ) I need value 1 need to there so the progress bar.

 

[pmyk]

In VB Editor,
Add a label and name it lblBack
Add another label and name it lblFace
Let both of them have equal height, say 12.
Let both of them have equal width, say 500.
Set lblFace right on lblBack.
Now, let lblFace have width 0.
Run the Code. Type 1000 in input box and see the bar scrolling.
-P. Michael
revpmichael (at) yahoo (dot) com
======== ========= ===============
'Code in Userform1 is given below
'Command1 is in Userform1
Private z As Integer
'Private varBackLabel_Width As Integer
Public maxr As Long

Private Sub Command1_Click()
maxr = InputBox("Type Total No of records to be entered:", "No of Records!")
'MsgBox "maxr =" & maxr & "."
Call PBarLabelsShow
For z = 1 To maxr
ProgBar maxr
Static m As Long
'Debug.Print "before incrementing m = " & m
m = m + 1
'Debug.Print "after incrementing m = " & m
Select Case m
Case 1
Userform1.ListBox1.AddItem "1st m =" & m & "."
Case 2
Userform1.ListBox1.AddItem "2nd m = " & m & "."
Case 3
Userform1.ListBox1.AddItem "3rd m = " & m & "."
Case 4
Userform1.ListBox1.AddItem "4th m = " & m & "."
Case 5
Userform1.ListBox1.AddItem "5th m = " & m & "."
Case 6 To 9
Userform1.ListBox1.AddItem "6th to 9th m = " & m & "."
Case Else
Userform1.ListBox1.AddItem "Else 9 or above m = " & m & "."
End Select
If m = maxr Then
m = 0
End If
Next z
Call PBarLabelsHide
End Sub
Private Sub PBarLabelsShow()
Userform1.lblBack.Visible = True
Userform1.lblFace.Visible = True
End Sub
Private Sub PBarLabelsHide()
Userform1.lblBack.Visible = False
Userform1.lblFace.Visible = False
End Sub

==== ========== =========
'Code in Module1 is given below
'Private m_iMin As Integer
Private m_iMaxValue As Integer
Private m_iIncre As Integer
Private m_sWidth As Long
Private m_iValue As Long
Private y As Long
Private varWidOld As Long
Private PBMax As Long
Private AllMax As Long
Function ProgBar(PBMax As Long)
AllMax = PBMax
'Debug.Print "Function max" & AllMax
'MsgBox "in function. PBMax=" & PBMax & "."
m_iMaxValue = 1
m_iIncre = 1
Call PopulateListr
End Function
Private Sub PopulateListr()
Static varNewiValue As Integer
If varNewiValue = 0 Then
'MsgBox "going to clear listbox"
Userform1.ListBox1.Clear
End If
varNewiValue = varNewiValue + m_iIncre
m_sWidth = (Userform1.lblBack.Width / AllMax) * varNewiValue
'm_iValue = Abs(varNewiValue - m_iMin) + 1
m_iValue = varNewiValue
'Debug.Print "m_iValue = " & m_iValue
Static varWid As Long
'MsgBox "varWid=" & varWid & vbCrLf & _
"varWidOld=" & varWidOld & vbCrLf & _
"m_sWidth=" & m_sWidth
If varWid >= Userform1.lblBack.Width Then
varWid = 0
End If
'varWid = varWid + (m_iValue * m_sWidth) / m_iMaxValue
varWid = m_sWidth
varWidOld = varWid
'Debug.Print "m_iValue * 100 / AllMax = " & Int(m_iValue * 100 / AllMax)
With Userform1.lblFace
.Width = varWid
'.Caption = CStr(Int(m_iValue * 100 / m_iMaxValue)) & "%" 'CStr(Int(1 * 100 / 50)) & "%"
.Caption = CStr(Int(m_iValue * 100 / AllMax)) & "%" 'CStr(Int(1 * 100 / 50)) & "%"
End With
If varNewiValue = AllMax Then
varNewiValue = 0
m_iValue = 0
End If
DoEvents
Select Case AllMax
Case Is <= 50
For y = 1 To 1000000
Next y
Case Is <= 100
For y = 1 To 100000
Next y
Case Is <= 1000
For y = 1 To 10000
Next y
Case Is > 1000
'Do Nothing
End Select
End Sub
-P. Michael
revpmichael (at) yahoo (dot) com