Userform Error

[Stildawn]

Hi All

I am working on a calculator using a Userform.

What I want is for just the userform to open, and to do that I have used this code:

Private Sub Workbook_Open()
'Un"'" the below to turn on
ActiveWindow.WindowState = xlMinimized
Application.Visible = False
FilterNames.Show
End Sub

As you can see this code makes excel open the workbook as invisable leaving just ther userform (FilterNames) showing, this works as intended.

Here is all the code for the userform itself:

Private Sub CommandButton1_Click()
Rate1.Caption = CStr(ThisWorkbook.Sheets("Sheet1").Range("E1").Text)
End Sub

Private Sub Cubic_Change()
Sheet1.Range("D1") = Cubic.Value
End Sub

Private Sub FilteredList_Click()
Sheet1.Range("B1") = FilteredList.Value
End Sub

Private Sub Rate_Change()
End Sub

Private Sub Rate1_Click()
Rate1.Caption = Format(Rate1.Caption, "0.00")
End Sub

Private Sub UserFilter_Change()
With Workbooks("FRT Calculator Mastercopy.xlsm")
Sheets("FilteredNames").Cells.ClearContents
For X = 1 To Sheets("Rates").Range("A" & Rows.Count).End(xlUp).Row
    If UCase(Left(Sheets("Rates").Range("A" & X).Value, Len(UserFilter))) = UCase(UserFilter) Then
        If Sheets("FilteredNames").Range("A1") = "" Then
            Sheets("FilteredNames").Range("A1").Formula = Sheets("Rates").Range("A" & X).Text
        Else
            Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Sheets("Rates").Range("A" & X).Text
        End If
    End If
Next
FilteredList.RowSource = "FilteredNames!A1:A" & Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Row
End With
End Sub

Private Sub Userform_QueryClose(Cancel As Integer, closemode As Integer)
    If closemode = vbFormControlMenu Then
        Application.DisplayAlerts = False
        If Workbooks.Count > 1 Then
        ThisWorkbook.Close True
        Workbooks(1).Activate
        Else
        Application.Quit
        End If
        Cancel = True
    End If
End Sub
 
Private Sub Weight_Change()
Sheet1.Range("C1") = Weight.Value
End Sub

This works fine as is also, however if you try both codes together (as in the make excel invisable with the userform) then I get a Run Time Error 9, Subscript out of Range, when I click debug it points to this line:

Sheets("FilteredNames").Cells.ClearContents

It seems to me that if excel is invisable the userform can't find the worksheet "FilteredNames"?? I have double checked all the spelling etc so that's fine. Basically as it is now I have just disabled the invisable code, so the user sees the sheet behind the userform etc when opening. However ideally I would love to have excel invisable, so hopefully you guys have some ideas on how to fix the above.

Further on from the above, if we can get the invisable code to work, would anyone have an idea how I could write the invisable code so that if the user opens this workbook by itself it hides all of excel, however if the user opens this workbook while they have other workbooks open it only hides this workbook while the other ones remain visable.


Thanks in advance for all your help.

 

[xenou]

Hi,
I think when using a With Block you must be careful about how you "chain" the child objects to the parent objects, using dot notation:

Private Sub UserFilter_Change()
With Workbooks("FRT Calculator Mastercopy.xlsm")
Sheets("FilteredNames").Cells.ClearContents
For X = 1 To Sheets("Rates").Range("A" & Rows.Count).End(xlUp).Row
    If UCase(Left(Sheets("Rates").Range("A" & X).Value, Len(UserFilter))) = UCase(UserFilter) Then
        If Sheets("FilteredNames").Range("A1") = "" Then
            Sheets("FilteredNames").Range("A1").Formula = Sheets("Rates").Range("A" & X).Text
        Else
            Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Sheets("Rates").Range("A" & X).Text
        End If
    End If
Next
FilteredList.RowSource = "FilteredNames!A1:A" & Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Row
End With
End Sub

All the Sheets references in the above code should be dotted:

Private Sub UserFilter_Change()
With Workbooks("FRT Calculator Mastercopy.xlsm")
.Sheets("FilteredNames").Cells.ClearContents
For X = 1 To .Sheets("Rates").Range("A" & Rows.Count).End(xlUp).Row
    If UCase(Left(.Sheets("Rates").Range("A" & X).Value, Len(UserFilter))) = UCase(UserFilter) Then
        If .Sheets("FilteredNames").Range("A1") = "" Then
            .Sheets("FilteredNames").Range("A1").Formula = .Sheets("Rates").Range("A" & X).Text
        Else
            .Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Formula = .Sheets("Rates").Range("A" & X).Text
        End If
    End If
Next
FilteredList.RowSource = "FilteredNames!A1:A" & .Sheets("FilteredNames").Range("A" & Rows.Count).End(xlUp).Row
End With
End Sub

 

[Stildawn]

I only added the with block in later as it seemed to have difficulty finding the sheets to use on other peoples computers, I guess since I found out that the visability code was the issue I could remove the with block completely?

Will try the dot notations above though can't hurt. Thanks

 

[Stildawn]

Tried the above with the invisable code on, it seems to work on my machine if its the only workbook open, however if I open some other excel sheets, then this one, it comes back with plenty of errors: 380's 1004's etc

 

[xenou]

Hi,
Your code as written is meant to run on a specific named workbook and on specific named worksheets in that workbook. Other workbooks open or closed are irrelevant.

ξ

 

[Stildawn]

Yeah thats what I thought (and how I wrote it). Tis why I am here I don't understand whats going on to be honest as I can't see why the other workbooks being open effect anything, but they do.

 

[xenou]

Post your code as it is now.
ξ

 

[Stildawn]

Well I went back to my original since that is working (the code in the OP). The issue is that when I turn on the application.visable = false line in my openworkbook code. If you had other workbooks open already then you get the 9 subscript out of range error on this line:

Sheets("FilteredNames").Cells.ClearContents

 

[xenou]

hi,
Your original code is malformed because it isn't chained properly. I wouldn't expect any kind of consistent results from it. So the rest is moot. Using a Sheets(x) without being dotted to a parent workbook will result in code being executed on the active workbook (whatever workbook is active). So the results will be dependent on the (random) event of whatever workbook happens to open at the time.


Note: it is also possible that from a userform there is no active workbook in scope. If so, the answer is still the same - get yourself a workbook reference and use it!
ξ

 

[Stildawn]

All the code above is the userform code (as in Alt F11 > Forms > FilteredNames > View Code), (other than the workbook open code). So would you be able to tell me how to lock down all the above code to use just "FRT Calculator Mastercopy.xlsm" as your explaination makes sense as it only happens if there are other workbooks present.

Do I need to put a With Workbook("FRT Calculator Mastercopy.xlsm") block on each sub or something?

Thanks in advance