Macro for CheckBox

[jonreyno]

Hi All,

I have the following code which works apart from 3 aspects:

When you click 'Cancel' to close the 'Select File' window, the cell where the hyperlink is set is populated with 'FALSE' - Can this be altered so when you cancel the window it doesn't put anything in the cell?
When you click 'Cancel' to close the window, the Checkbox still leaves the tick in the box - Can this be altered that when you click 'Cancel' the box is left unticked?
The hyperlink address shows the full path to the file and I would like only the file name. THe code is:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Dim vFile As Variant, Sh As Object, rownum As Long, colnum As Long
rownum = 43 'for example
colnum = 11 'for example
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert", MultiSelect:=False)
If vFile = "false" Then
End Sub
Cells(rownum, colnum).Formula = "=HYPERLINK(""" & vFile & """)"
End Sub

Any advice would be great.

Thanks
Jon

[JKwan]

Try this
Private Sub CheckBox1_Click()
Dim vFile As Variant, Sh As Object, rownum As Long, colnum As Long
rownum = 43 'for example
colnum = 11 'for example
If CheckBox1.Value = True Then
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert", MultiSelect:=False)
End If
If vFile <> "False" Then
Cells(rownum, colnum).Formula = "=HYPERLINK(""" & vFile & """)"
Else
CheckBox1.Value = False
Cells(rownum, colnum) = ""
End If
End Sub

[jonreyno]

Try this
Private Sub CheckBox1_Click()
Dim vFile As Variant, Sh As Object, rownum As Long, colnum As Long
rownum = 43 'for example
colnum = 11 'for example
If CheckBox1.Value = True Then
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert", MultiSelect:=False)
End If
If vFile <> "False" Then
Cells(rownum, colnum).Formula = "=HYPERLINK(""" & vFile & """)"
Else
CheckBox1.Value = False
Cells(rownum, colnum) = ""
End If
End Sub

Hi JKwan,

This code is almost perfect, the only thing that isn't working is the Hyperlink is showing the full path name and not just the file name. Are you able to include this in the coding?

I have an alternative code which has been put together that gives the filename but the checkboxes don't work in the same way as your code (which is better). I have provided that code so you can see what they have put as I can't figure out what part would need to be changed:


Dim vFile As Variant, Sh As Object, rownum As Long, colnum As Long
Dim friendly As String
Dim slashloc As Integer
rownum = 43 'for example
colnum = 11 'for example
vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert", MultiSelect:=False)
If LCase(vFile) = "false" Then Exit Sub
slashloc = Len(vFile)
Do
If Mid(vFile, slashloc, 1) = "\" Then Exit Do
slashloc = slashloc - 1
Loop Until slashloc = 0
If slashloc = 0 Then
friendly = vFile
Else
friendly = Mid(vFile, slashloc + 1, Len(vFile) - slashloc)
End If
Cells(rownum, colnum).Formula = "=HYPERLINK(""" & vFile & """, """ & friendly & """)"

As you can see the code is slightly long winded but the file name comes out how and I want it, so if it can be merged into your code, that would be ideal.

[JKwan]

just update this:
Cells(rownum, colnum).Formula = "=HYPERLINK(""" & Mid(vFile, InStrRev(vFile, "\") + 1, Len(vFile) - InStrRev(vFile, "\")) & """)"