how to use CheckBox to enable/disable vba-code

[APOC [T.I.M.]]

I use this code to auto-sort (and auto-hyperlink) my document:

' Auto-Hyperlink on Column:A  &  Auto-SORT  !!! !!! !!! !!! ! '
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Sh As Worksheet
    Dim rng As Range
    '   only look at single cell changes
    Dim Cell As Range
    Set Sh = Worksheets("DVD Lijssie")
    Set rng = Sh.Range("A4:A" & Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row)
    '   only look at that range
    For Each Cell In rng
        If Cell.Hyperlinks.Count = 0 Then
            Sh.Hyperlinks.Add Cell, "http://www.imdb.com/find?s=all&q=" & Cell.Value
            With Cell.Font
                .Name = "Arial Narrow"
                .Size = 8
            End With
            '   Cell.Resize(, 7).BorderAround ColorIndex:=6, Weight:=xlThin
            '   ColorIndex:=15 <= For LightGray
            ' The Resize property takes 2 arguments, RowSize and ColumnSize.
            ' If an argument is omitted, the number remains the same.
            ' So: Set rng = rng.Resize(, 7)
            ' expands the existing rng to 7 columns,
            ' retaining the existing number of rows.
        End If
    Next Cell
    
        If Target.Count > 1 Then Exit Sub
        Set rng = rng.Resize(, 7)
        ' The Resize property takes 2 arguments, RowSize and ColumnSize.
        ' If an argument is omitted, the number remains the same.
        ' So: Set rng = rng.Resize(, 7)
        ' expands the existing rng to 7 columns,
        ' retaining the existing number of rows.
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
 
End Sub

Now i'd like to add somekind of CheckBox so I can enable and/or disable the code.
ie.:

If Checkbox1 Then
etc.
Else
etc.
End if

BUT I don't know exactly how to, could somebody help me out?
It would be great that when you enable the code again that it gets/stays active AND/BUT first run/start the code once, I mean automatically sorts (and hyperlink) the document on activating CheckBox.
Perhaps somehow call worksheet_change or what ever so the code will run/start?
I hope I explained it well enough?

With kind regards, Tim

 

[APOC [T.I.M.]]

Stop script from running

Is there a simple way to STOP the code as stated above when clicking on a checkbox? OR at least stop the auto-sort part, if the auto-hyperlink part (in column: A) should still continue this wouldn't be a problem at all.
I need to stop the Sort part of the code, beacause when i'd like to add a lot of new lines the auto-sort gets pretty annoying, now I work around it by stopping all VBA (Alt-F11 & Pause). But i'd like it to work just as "simple" as clicking on a checkbox so only the auto-sort stalls/holds/pause and all the other VBA scripts keep on running.

 

[APOC [T.I.M.]]

SOLVED?!

IT SEEMS that i've figured it out 

' Auto-Hyperlink on Column:A  &  Auto-SORT  !!! !!! !!! !!! ! '
Private Sub Worksheet_Change(ByVal Target As Range)
' (START) Auto-HYPERLINK by DEFAULT !!! !!! !!! !!! !!! !!! ! '
    Dim Sh As Worksheet
    Dim rng As Range
    '   only look at single cell changes
    Dim Cell As Range
    Set Sh = Worksheets("DVD Lijssie")
    Set rng = Sh.Range("A4:A" & Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row)
    '   only look at that range
    For Each Cell In rng
        If Cell.Hyperlinks.Count = 0 Then
            Sh.Hyperlinks.Add Cell, "http://www.imdb.com/find?s=all&q=" & Cell.Value
            With Cell.Font
                .Name = "Arial Narrow"
                .Size = 8
            End With
            '   Cell.Resize(, 7).BorderAround ColorIndex:=6, Weight:=xlThin
            '   ColorIndex:=15 <= For LightGray
        End If
    Next Cell
' (END) Auto-HYPERLINK by DEFAULT !!! !!! !!! !!! !!! !!! !! '
If CheckBox1 Then
' (START) Auto-SORT on Check(Box1) !!! !!! !!! !!! !!! !!! ! '
        If Target.Count > 1 Then Exit Sub
        Set rng = rng.Resize(, 7)
        ' The Resize property takes 2 arguments, RowSize and ColumnSize.
        ' If an argument is omitted, the number remains the same.
        ' So: Set rng = rng.Resize(, 7) => expands the existing rng to 7 columns,
        ' retaining the existing number of rows.
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' (END) Auto-SORT on Check(Box1) !!! !!! !!! !!! !!! !!! !!! '
Else
' DO NOTHING AT ALL
End If
End Sub

I Don't know if it should be done otherwise? but for the moment the code seems to work like it should AND like I wan't it to work.
IF someone has some other insides or vision(s) on the code i'd really like to know ofcourse, I hope I coded it right, at least it seems allright for now.

OR should I use this code?

' Auto-Hyperlink on Column:A  &  Auto-SORT  !!! !!! !!! !!! ! '
Private Sub Worksheet_Change(ByVal Target As Range)

' (START) Auto-HYPERLINK by DEFAULT !!! !!! !!! !!! !!! !!! ! '
    Dim Sh As Worksheet
    Dim rng As Range
    '   only look at single cell changes
    Dim Cell As Range
    Set Sh = Worksheets("DVD Lijssie")
    Set rng = Sh.Range("A4:A" & Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row)
    '   only look at that range
    For Each Cell In rng
        If Cell.Hyperlinks.Count = 0 Then
            Sh.Hyperlinks.Add Cell, "http://www.imdb.com/find?s=all&q=" & Cell.Value
            With Cell.Font
                .Name = "Arial Narrow"
                .Size = 8
            End With
            '   Cell.Resize(, 7).BorderAround ColorIndex:=6, Weight:=xlThin
            '   ColorIndex:=15 <= For LightGray
        End If
    Next Cell
' (END) Auto-HYPERLINK by DEFAULT !!! !!! !!! !!! !!! !!! !! '

' If CheckBox1 Then ' Previous version
If CheckBox1.Value = True Then
' (START) Auto-SORT on Check(Box1) !!! !!! !!! !!! !!! !!! ! '
        If Target.Count > 1 Then Exit Sub
        Set rng = rng.Resize(, 7)
        ' The Resize property takes 2 arguments, RowSize and ColumnSize.
        ' If an argument is omitted, the number remains the same.
        ' So: Set rng = rng.Resize(, 7) => expands the existing rng to 7 columns,
        ' retaining the existing number of rows.
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' (END) Auto-SORT on Check(Box1) !!! !!! !!! !!! !!! !!! !!! '

Else
CheckBox1.Value = False ' added in this new version
' DO NOTHING AT ALL WHEN FALSE/UNCHECKED/DISABLED
End If

End Sub

=> Used If CheckBox1.Value = True Then instead of If CheckBox1 Then and added CheckBox1.Value = False after Else
In my opinion this is a better way of doing the same isn't it?

 

[APOC [T.I.M.]]

enable checkbox by default?

is it possible to somehow set the CheckBox1 to on/enabled bij default?
perhaps call or enable it or whatever in ThisWorkbook (opening the .xls doc)

This doesn't seem to work:

Private Sub Workbook_Open()
' Set Auto-SORT to ENABLED by Default
CheckBox1.Value = True
End Sub

 

[APOC [T.I.M.]]

Enable and/or Check a (specific) CheckBox by DEFAULT ???

Is it possible to somehow set the CheckBox1 to enabled/checked by default?
Perhaps call or enable it or whatever in ThisWorkbook (on opening the .xls document)

This doesn't seem to work:

Private Sub Workbook_Open()
' Set Auto-SORT to ENABLED by Default
CheckBox1.Value = True
End Sub

And to somehow immediately run the code when checking/enabling CheckBox1? So when I enable/check CheckBox1 the code immediately starts sorting (and hyperlinking) and then stay's active for for Worksheet_Change offcourse.

with kind regards, Tim

 

[APOC [T.I.M.]]

SORRY

STUPID ME, I've got it, when using Workbook instead of WorkSheet I need to define a/the sheet...

This is how I got it to work now

Private Sub Workbook_Open()
' Set Auto-SORT to ENABLED & Exact-Filter to FALSE by Default
Set Sh = Worksheets("DVD Lijssie")
Sh.CheckBox1.Value = True
Sh.CheckBox2.Value = False
End Sub

Excuse me for my ignorance, "problem" solved!