ActiveX controls

[rickblunt]

Greetings, I have been using userforms for years and I have really enjoyed it - but I have never really tried to put the technique (other than adding command buttons) directly on the worksheets. So I am trying my hand at building a dashboard and I thought that this would be the perfect time to start embedding the controls into the WS instead of calling up a userform. I now realize though that the syntax must be different as the code below is not acting the same as it does in my userform.

Briefly, I use the value in a combobox (RepairedDevice) to sort data and pass it to a listbox (RepairHistory). Works great in my userforms, but nothing really appears to happen other than moving to the Repair Log sheet. I would normally put the code in a module, but I found that I actually had to put it into the WS page module instead for it to even see the objects.

Is it that I am using the objects incorrectly and that the syntax is completely different - or am I missing something simple? Because the code is in the Dashboard WS but the data is in the Repair Log WS leads me to think that I cant pass data between sheets or something like that.

I appreciate your thoughts on this - thank you,

Sub DashboardRepairs()
    Dim i As Long
    Dim Lastrow As Long
      
 Application.DisplayAlerts = False
        
    Sheets("Repair Log").Select

    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
         For i = 1 To Lastrow
             If Cells(i, 1).Value = RepairedDevice.Value Then RepairHistory.AddItem Cells(i, 1).Value & " - " + Cells(i, 2).Value & _
             " - " + Cells(i, 3).Value & " - " + Cells(i, 4).Value & " - " + Cells(i, 5).Value
         Next
         
   Sheets("Dashboard").Select
         
 Application.DisplayAlerts = True
  
End Sub

 

[DanteAmor]

As I understand it, the ActiveX Controls are on sheet "Dashboard"
Try this:

Sub DashboardRepairs()
  Dim sh As Object
  Dim i As Long
  
  Set sh = Sheets("Dashboard")
  With Sheets("Repair Log")
    sh.RepairHistory.Clear
    For i = 1 To .Range("A" & Rows.Count).End(3).Row
      If .Range("A" & i).Value = sh.RepairedDevice.Value Then
        sh.RepairHistory.AddItem .Cells(i, 1).Value & " - " & .Cells(i, 2).Value & " - " & _
                              .Cells(i, 3).Value & " - " & .Cells(i, 4).Value & " - " & .Cells(i, 5).Value
      End If
    Next
  End With
End Sub

 

[rickblunt]

Thanks Dante - that worked pretty well. So the syntax is basically the same as what I am used to, with a few nuances - that's good to know; Hopefully I can adapt without much trouble. The only quirk was that it was only transferring over the first two columns, not all five. I have my listbox set for 5 columns, and I gave them all a pretty large width.... So I will have to figure out why only two columns show up - thanks for the help.

 

[rickblunt]

Thanks Dante - that worked pretty well. So the syntax is basically the same as what I am used to, with a few nuances - that's good to know; Hopefully I can adapt without much trouble. The only quirk was that it was only transferring over the first two columns, not all five. I have my listbox set for 5 columns, and I gave them all a pretty large width.... So I will have to figure out why only two columns show up - thanks for the help.

Follow up - I figured it out, all five columns on the WS is getting passed to the first column on the listbox, not 5 separate columns like on the WS.

 

[DanteAmor]

The only quirk was that it was only transferring over the first two columns, not all five. I have my listbox set for 5 columns

For that, try this:

Sub DashboardRepairs()
  Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
    
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 1).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 5).Value
      End With
    End If
  Next
End Sub

 

[rickblunt]

Sweet - Thanks Dante, one column would work but all 5 is the best (the formatting will look better) best of all I can understand how you are doing it in the code Maybe there is hope for me yet - lol.. Thank you for the assistance and yet one more knowledge session.

 

[DanteAmor]

Nice to hear that. Thanks for the feeedback.