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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,381
Messages
5,510,960
Members
408,820
Latest member
MugsUpMan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top