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 !
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,477
Messages
5,831,900
Members
430,090
Latest member
bjonesh2o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top