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:
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:
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:
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.
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:
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:
Code:
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:
Code:
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.