Userform disappears when moving from one refedit to another in an tabstrip.

[Traderjoe56]

Hi everyone,

I'm trying to create a "Workbook Aggregator". That is an excel file, that given a variable range, and some data, can match the variables automatically, and aggregate all the data into a single excel file. What I'm doing is looping through files in a directory, opening each file, and asking my user where the variables and data is in a tabstrip userform. Refedits help collect the addresses.(in this case, the variable range, where the data starts, and where it ends.

What I'd like to do is to collect those addresses from the refedits, and put it into a sheet.

The problem I'm running into is puzzling. basically, I can open a file at a time, have the userform create a tabstrip for each strip, and have it collect checkbox data (In this case, whether there's useful information on that sheet or not, but when someone clicks on the refedit (say the one for variable range). it'll do what it needs to do and write an address on the refedit control.

The minute you click on another refedit though, the entire userform magically disappears, and code execution stops. I have no idea what's causing this. I tried changing refedit events, and that doesn't seem to work either. Here's the sheet "Workbench" that collects the data for each workbook:

Workbook	Sheets	Lastrow	LastColumn	Variable Range	Start Cell Address	End Cell Address	No Useful Data
Workbook1.xls	Instructions	14	1				1
Workbook1.xls	Data	126	4				0
Workbook1.xls	Formula Results	4972	24				0
Workbook1.xls	Codes	120	1				1

<tbody>
</tbody>

Here's the userform:

[/IMG]



And here's the codes in the userform (you'll have to pardon the messiness, I'm still new at VBA):

Private Sub UserForm_initialize()
'*********************************************************************************
'This finds the number of sheets in the open workbook, renames the tab strips to the sheet names, and fills in values
'depending on the active tab strip.
'
'*********************************************************************************
Dim tabname As String


Totsheets = ActiveWorkbook.Sheets.Count - 1
Actrow = 2
'checks whether total tabs in the userform is less than the number of sheets in the active workbook.
'if it is, it creates tabs.
If TabStrip1.Tabs.Count < Totsheets Then
    newtabTC = Totsheets - TabStrip1.Tabs.Count
    For createnewtabs = 1 To newtabTC
        TabStrip1.Tabs.Add
    Next createnewtabs
End If


'labels each tab with the sheet name of the opened excel file.
For Tablabel = 0 To Totsheets - 1
    TabStrip1.Tabs(Tablabel).Caption = ThisWorkbook.Sheets("workbench").Cells(Actrow, 2).Value
    Actrow = Actrow + 1


Next Tablabel
ActiveWorkbook.Sheets(1).Activate


End Sub


Private Sub ExitDSV_Click()
    Unload Me
End Sub


Private Sub ShtBlnk_change()
Finalrow = ThisWorkbook.Sheets("Workbench").Cells(Rows.Count, 1).End(xlUp).Row
'helps to identify the rows for the active workbook.
For actwrkbkRow = 2 To Finalrow
    If ThisWorkbook.Sheets("Workbench").Cells(actwrkbkRow, 1) = ActiveWorkbook.Name Then
        Actrow = actwrkbkRow
        Exit For
    End If
Next actwrkbkRow


    If ShtBlnk.Value = True Then
        'disable all dialogues if checkbox is checked
        VarRnge.Enabled = False
        CellStart.Enabled = False
        CellEnd.Enabled = False
        ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 8) = 1
        
    Else
        VarRnge.Enabled = True
        CellStart.Enabled = True
        CellEnd.Enabled = True
        ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 8) = 0
    End If
End Sub


[B]Private Sub VarRnge_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Frow = ThisWorkbook.Sheets("Workbench").Cells(Rows.Count, 1).End(xlUp).Row
'helps to identify the rows for the active workbook.
For shtindx = 2 To Frow
    If ThisWorkbook.Sheets("Workbench").Cells(shtindx, 1) = ActiveWorkbook.Name Then
        Actrow = shtindx
        Exit For
    End If
Next shtindx


'puts VarRnge value into workbench
    ThisWorkbook.Sheets("Workbench").Cells(Actrow + TabStrip1.Value, 5) = VarRnge.Value
End Sub


Private Sub CellStart_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Finalrow = ThisWorkbook.Sheets("Workbench").Cells(Rows.Count, 1).End(xlUp).Row
'helps to identify the rows for the active workbook.
For actwrkbkRow = 2 To Finalrow
    If ThisWorkbook.Sheets("Workbench").Cells(actwrkbkRow, 1) = ActiveWorkbook.Name Then
        Actrow = actwrkbkRow
        Exit For
    End If
Next actwrkbkRow


'puts CellStart value into workbench
        ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 6) = CellStart.Value
End Sub


Private Sub CellEnd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Finalrow = ThisWorkbook.Sheets("Workbench").Cells(Rows.Count, 1).End(xlUp).Row
'helps to identify the rows for the active workbook.
For actwrkbkRow = 2 To Finalrow
    If ThisWorkbook.Sheets("Workbench").Cells(actwrkbkRow, 1) = ActiveWorkbook.Name Then
        Actrow = actwrkbkRow
        Exit For
    End If
Next actwrkbkRow


'puts CellEnd value into workbench
        ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 7) = CellEnd.Value
End Sub
[/B]
Private Sub TabStrip1_Change()
Finalrow = ThisWorkbook.Sheets("Workbench").Cells(Rows.Count, 1).End(xlUp).Row
'helps to identify the rows for the active workbook.
For actwrkbkRow = 2 To Finalrow
    If ThisWorkbook.Sheets("Workbench").Cells(actwrkbkRow, 1) = ActiveWorkbook.Name Then
        Actrow = actwrkbkRow
        Exit For
    End If
Next actwrkbkRow
'change focus to active tab strip
    Dim lngRow As Long
    lngRow = TabStrip1.Value + 1
    ActiveWorkbook.Sheets(lngRow).Activate


'set values of tab strip according to workbench
    ShtBlnk.Value = ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 8)
    VarRnge.Value = ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 5)
    CellStart.Value = ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 6)
    CellEnd.Value = ThisWorkbook.Sheets("workbench").Cells(Actrow + TabStrip1.Value, 7)


End Sub

so again, the problem is, clicking on a refedit control, after any other refedit control makes the userform completely dissapear, and code execution to stop. It also fails to put the refedit addresses in workbench, and putting breakpoints on the codes for the refedits doesn't seem to work.

Can anyone help?

Much much thanks in advance for hacking at this!

Joe