User Form functionality

Sweedler

Board Regular
Joined
Nov 13, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hello EXCEL community,

So I have a large document divided into several sections. See pic below for basic appearance:
1608299507271.png


In the entire sheet, the sections in green are the only things that are in column A. I want to create a user form that allows me automatically create a checklist for all the sections, to be able to DOUBLE CLICK a section in the user form list to navigate there and. I ave seen this done and have looked at that document but have not been able to recreate the effect.

Here is the code I'm using to navigate via doubleclicking (all code from USERFORM)
VBA Code:
Private Sub lvSektioner_DblClick()
    For i = 17 To 1000
        If shNormal.Range("A" & i).Value = Me.lvSektioner.SelectedItem Then
            shNormal.Activate
            shNormal.Range("A" & i).Select
            Exit Sub
        End If
    Next i
End Sub

i think this one creates the list on the USERFORM:
VBA Code:
Private Sub lvSektioner_Markera(intAlt As Integer)
    Dim lv As MSComctlLib.ListView
    Dim li As MSComctlLib.ListItem
    Dim b As Boolean
   
    Set lv = Me.lvSektioner
   
    b = IsSheetProtected(shNormal)
    If b Then
        shNormal.Unprotect "sb123"
    End If
   
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
   
    For Each li In lv.ListItems
        Select Case intAlt
        Case 1 'Alla
            If li.Checked = False Then
                li.Checked = True
                VisaSektion True, Mid(li.Key, 4)
            End If
        Case 2 'Endast låst design
            If li.SubItems(1) = "X" Then
                If li.Checked = False Then
                    li.Checked = True
                    VisaSektion True, Mid(li.Key, 4)
                End If
            Else
                If li.Checked = True Then
                    li.Checked = False
                    VisaSektion False, Mid(li.Key, 4)
                End If
            End If
        Case 3
            If li.SubItems(1) = "X" Then
                If li.Checked = True Then
                    li.Checked = False
                    VisaSektion False, Mid(li.Key, 4)
                End If
            Else
                If li.Checked = False Then
                    li.Checked = True
                    VisaSektion True, Mid(li.Key, 4)
                End If
            End If
        End Select
    Next li
   
    If b Then
        shNormal.Protect "sb123"
    End If
   
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
   
End Sub

Right now this is the CODE the seems to freeze the entire process:
VBA Code:
Private Sub lvSektioner_ItemCheck(ByVal Item As MSComctlLib.ListItem)
    Dim li As MSComctlLib.ListItem
    Dim lRad As Long
    Dim b As Boolean
   
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
   
    b = IsSheetProtected(shNormal)
    If b Then
        shNormal.Unprotect "sb123"
    End If
   
    Set li = Item
    lRad = Mid(Item.Key, 4)
   
    If li.Checked Then
        VisaSektion True, lRad
    Else
        VisaSektion False, lRad
    End If
   
    If b Then
        shNormal.Protect "sb123"
    End If
   
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
   
End Sub
 
Last edited by a moderator:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,214,944
Messages
6,122,391
Members
449,080
Latest member
Armadillos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top