Userform control function not working in Excel 2000

bullneck

New Member
Joined
May 10, 2010
Messages
12
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 :mad:

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 !
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

bullneck

New Member
Joined
May 10, 2010
Messages
12
....forgot to say..... there is no error message, when the command button is pressed nothing happens....the code runs but does not do anything ? (viewed using break steps)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,728
Messages
5,524,486
Members
409,584
Latest member
RedHelp

This Week's Hot Topics

Top