We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Modify Len Formula

Cartillo asked
Medium Priority
Last Modified: 2012-06-21
Hi Experts,

I would like to request Experts help. How to make the attached formula more flexible (Module at Sheet 5 (Chart2) so that when I amend the “title” at C41 (Chart2) doesn’t impact the Userfome  detail display. At present we can click the chart line by targeting the line for userfome display. If there was issue highlighted for that week, Userform will display related detail of the “Type” selection from Daily Tracking List sheet at  Userform. All selection (selection of type can be made by using Spinner) are working fine except the type under “testing” (column N at “Chart” sheet) category. I believe this is due to text length. Hope Experts will help me to solve this problem.

If Len(Cells(41, 3)) = 11 Then
      mem = Mid(Cells(41, 3), 8, 3)
        mem = Mid(Cells(41, 3), 8, 4)

Open in new window

Watch Question

You mean like this?

Private Sub lblChart_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    'On Error GoTo lblChart_Err
    Dim objSheet As Worksheet
    Dim iWeekNumber As Integer
    Dim iWeekNumberRow As Integer
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim iDailyRow As Integer
    Dim dtIssueDate As Date
    Dim mem As Variant
    Dim MyArray() As String
    MyArray = Split(Cells(41, 3), "(")
    mem = Left(MyArray(0), Len(MyArray(0)) - 1)
    lblChart.Visible = False
    Set objSheet = Sheets("Daily Tracking List")
    iWeekNumber = Int(X / (lblChart.Width / 54) + 1)
    If iWeekNumber = 0 Then iWeekNumber = 1
    iWeekNumberRow = GetWeekRow(iWeekNumber)
    dtStart = Sheets("Detail").Cells(iWeekNumberRow, 2).Value
    dtEnd = Sheets("Detail").Cells(iWeekNumberRow, 3).Value
    With UserForm1
         .Occurrence.Text = ""
         .Outage.Text = ""
         .TotalOutage.Text = ""
         .KPI.Text = ""
         .Updatesbox.Text = ""
        .DetailsBox.Text = ""
        .Updatesbox.Text = ""
    End With
    With UserForm1
        iDailyRow = 2
        Do Until objSheet.Cells(iDailyRow, 2).Value = ""
            dtIssueDate = objSheet.Cells(iDailyRow, 2).Value
            Select Case dtIssueDate
                Case Is < dtStart
                    ' keep looking
                Case Is <= dtEnd
                    ' process this
                    If objSheet.Range("D" & iDailyRow) = mem Then
                    .ListBox1.AddItem objSheet.Range("A" & iDailyRow).Value & ": " & objSheet.Range("B" & iDailyRow).Value & " " & Format(objSheet.Range("C" & iDailyRow).Value, "hh:mm:ss")
                    .DetailsBox.Text = objSheet.Range("J" & iDailyRow).Value
                    .Occurrence.Text = objSheet.Range("E" & iDailyRow).Value
                    .Outage.Text = objSheet.Range("G" & iDailyRow).Value
                    .TotalOutage.Text = objSheet.Range("H" & iDailyRow).Value
                    .KPI.Text = objSheet.Range("I" & iDailyRow).Value
                    .Status.Text = objSheet.Range("M" & iDailyRow).Value
                    .Updatesbox.Text = objSheet.Range("K" & iDailyRow).Value
                    '.Area.Text = objSheet.Range("?" & iDailyRow).Value
                    End If
                Case Else
                    ' assuming dates are in sequence, we're past the requested issue so we're done
                    Exit Do
            End Select
            iDailyRow = iDailyRow + 1
        If UserForm1.ListBox1.ListCount = 0 Then UserForm1.ListBox1.AddItem "No issues for week " & iWeekNumber
    End With
    Set objSheet = Nothing
    lblChart.Visible = True
    Exit Sub
    MsgBox "Error found: " & Err.Description
    Resume lblChart_Exit
End Sub

Open in new window

Amended File Attached



Hi Sid,

Thanks for the file. The detail information wasn’t displayed in the userform, e.g. using my original posting when we select “TH9”  type and  click at week 9 (plot area) the userfome will display all related info at List boxes. The similar things should display for “testing” type as well. Hope I’m not confusing you.


Hi Sid,

Hope my explanation in confusing you. What happen was the link for displaying the data which is C41 (Chart2) is not linking the “lblChart_MouseDown” module (sheet5) when I amend the “listing” sheet title. The line chart was plotted based on “Chart” sheet data. The cell C41 (Chart2) is actually mirroring cell V1 (Chart sheet). Hope this explanation gives you better understanding.



After test few data I noticed the connection totally depends on number of correctors that was used cell C41. Is that possible to set the corrector is  between 3 to 7

“If Len(Cells(41, 3)) = 11 Then
      mem = Mid(Cells(41, 3), 8, 3)”

Unlock this solution and get a sample of our free trial.
(No credit card required)


Thanks Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.