APOC [T.I.M.]
Board Regular
- Joined
- Jun 28, 2007
- Messages
- 132
I use this code to auto-sort (and auto-hyperlink) my document:
Now i'd like to add somekind of CheckBox so I can enable and/or disable the code.
ie.:
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
Code:
' 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
ie.:
Code:
If Checkbox1 Then
etc.
Else
etc.
End if
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