Hi,
I have created a userform (using Excel 2003) which searches for files using the users search criteria (part or full file name). Once criteria is confirmed, the macro identifies the valid file names and prints them onto the userform along with a corresponding checkbox.
The user then selects which files to view by checking the checkbox next to the filename. Once the files have been selected, the user presses a command button which runs a further macro which checks to see which checkboxes (filenames) have been selected. The selected files are then opened and copied onto a new tab within the worksheet.
This has been working without issue, however when I have ran the code on Excel2000 I have found the last part not to work. (checking to see which checkboxes have been selected and displaying the files)
I understand that I should have created the code using the oldest excel version, however I didnt realise the older version until I tried using it
The code used for this last part is below. I think the problem has something to do with the compatability of the Me.Controls between the two versions but I have very little experience using Excel2000..... ?
Any help/advice would be much appreciated !
I have created a userform (using Excel 2003) which searches for files using the users search criteria (part or full file name). Once criteria is confirmed, the macro identifies the valid file names and prints them onto the userform along with a corresponding checkbox.
The user then selects which files to view by checking the checkbox next to the filename. Once the files have been selected, the user presses a command button which runs a further macro which checks to see which checkboxes (filenames) have been selected. The selected files are then opened and copied onto a new tab within the worksheet.
This has been working without issue, however when I have ran the code on Excel2000 I have found the last part not to work. (checking to see which checkboxes have been selected and displaying the files)
I understand that I should have created the code using the oldest excel version, however I didnt realise the older version until I tried using it
The code used for this last part is below. I think the problem has something to do with the compatability of the Me.Controls between the two versions but I have very little experience using Excel2000..... ?
Code:
Sub CheckBox2() 'Test which checkbox is TRUE
Dim try As Control
Dim result As String
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each try In Me.Controls
If TypeName(try) = "CheckBox" Then
If try.Value = True Then
result = try.Caption
'============================================
'Loads data from each file in a folder into a seporate sheet in a defined workbook
'===========================================
'################ CHANGE PATH & FILENAME TO SUIT ##########################################
' MyPath = "C:\Users\Documents\" ' change to suit
Set wbDst = Workbooks("Fileviewer.xls") 'add sheets to defined workbook
'###########################################################################################
strFilename = Dir(MyPath & result, vbNormal) 'added RESULT from checkboxes
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
Set wsSrc = wbSrc.Worksheets(1)
wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
wbSrc.Close False
strFilename = Dir()
Loop
Application.DisplayAlerts = False
Application.EnableEvents = False 'True
Application.ScreenUpdating = False 'True
End If
End If
Next try
Sheets("FoundFiles").Select
End Sub
Any help/advice would be much appreciated !