asked on

Modify Len Formula

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)
    Else
        mem = Mid(Cells(41, 3), 8, 4)

Open in new window

Dynamic-Chart.xls

SiddharthRout

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 = ""
         .ListBox1.Clear
        .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
        Loop
        If UserForm1.ListBox1.ListCount = 0 Then UserForm1.ListBox1.AddItem "No issues for week " & iWeekNumber
        .Show
    End With
 
lblChart_Exit:
    Set objSheet = Nothing
    lblChart.Visible = True
    Exit Sub
    
lblChart_Err:
    MsgBox "Error found: " & Err.Description
    Resume lblChart_Exit
    Resume
End Sub

Open in new window

Sid

SiddharthRout

Amended File Attached

Sid
Dynamic-Chart.xls

Cartillo

ASKER

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.

Cartillo

ASKER

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.

Cartillo

ASKER

Hi,

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)”

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cartillo

ASKER

Thanks Sid