Error message "cant jump to sheet "FORM" because it is hidden"

M

maxshiner

Board Regular
Joined
Sep 24, 2014
Messages
71
I have been using this userform coding successfully, now it fails on the first line of code with the error message "can't jump to "Worksheets("Form").Range("B4") because it is hidden". I frequently have this problem with excel VBA. I am using 2013 Excel. What cause it to work for a length of time and then just quit working? I have included the code below. the worksheet Form is not hidden. Can some one please help me understand why it works sometime and not others?

Thanks

Code: 
Option Explicit
'///// Date

Worksheets("FORM").Range("B4") = TextBox1.Value
''   Transfer Date to Database
'Private Sub DTPicker1_Click()
'ClosedateTextBox = DatePicker1.Value
'Sheets("DATABASE").Select
    'Range("A3").Select
 'Selection.End(xlDown).Select
 'ActiveCell.Offset(1, 0).Select
 'Selection = DTPicker1.Value

'End Sub
 
Private Sub Okay_Click()
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// General Info about Closing (Listing Side Only)
'/////// Date
Worksheets("Tempdb").Range("A3") = _
          dateTextBox.Text
'/////// Address
Worksheets("Tempdb").Range("B3") = _
          AddressTextBox.Text
Worksheets("Form").Range("B3") = AddressTextBox.Text
Worksheets("Misc").Range("B43") = AddressTextBox.Text
Worksheets("LR1").Select
Range("B3") = AddressTextBox.Text
Worksheets("LR2").Select
Range("B3") = AddressTextBox

'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Seller Information
Worksheets("Tempdb").Range("AB3") = _
      SellerTextBox.Value
Worksheets("FORM").Range("B6") = SellerTextBox.Text
Worksheets("LR1").Range("B6") = SellerTextBox.Text
Worksheets("LR2").Range("B6") = SellerTextBox.Text
Worksheets("Misc").Range("B44") = SellerTextBox.Text
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Price Information
'//////// Listing Price
Worksheets("Tempdb").Range("D3") = PriceTextBox.Value
Worksheets("FORM").Range("D3") = PriceTextBox.Value
Worksheets("LR1").Range("D3") = PriceTextBox.Value
Worksheets("LR2").Range("D3") = PriceTextBox.Value
Worksheets("Misc").Range("B45") = PriceTextBox.Value
'//////// Selling Price
Worksheets("Tempdb").Range("G3") = "0"
'////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////Total Price
Worksheets("Tempdb").Range("I3") = Worksheets("Tempdb").Range("D3") _
+ Worksheets("Tempdb").Range("G3")
'//////// Internal Referrals
If Noreff Then
Worksheets("Misc").Range("F43") = "0"
Worksheets("Misc").Range("G43") = "0"
ElseIf OneInt Then
Worksheets("Misc").Range("F43") = "1"
Else: Worksheets("Misc").Range("F43") = "2"
End If
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Title Company
Worksheets("Tempdb").Range("AA3") = _
            ComboBoxTC.List(ComboBoxTC.ListIndex, 0)
Worksheets("Form").Range("B5") = ComboBoxTC.List(ComboBoxTC.ListIndex, 0)

Worksheets("LR1").Range("B5") = _
ComboBoxTC.List(ComboBoxTC.ListIndex, 0)

Worksheets("LR2").Range("B5") = _
ComboBoxTC.List(ComboBoxTC.ListIndex, 0)
Worksheets("Misc").Range("B46") = _
ComboBoxTC.List(ComboBoxTC.ListIndex, 0)
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Buyer
Worksheets("Tempdb").Range("AC3") = _
            BuyerTextBox.Text
Worksheets("FORM").Range("D6") = BuyerTextBox.Text
Worksheets("LR1").Range("D6") = BuyerTextBox.Text
Worksheets("LR2").Range("D6") = BuyerTextBox.Text
Worksheets("Misc").Range("B47") = BuyerTextBox.Text
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Listing Agent
Worksheets("Tempdb").Range("J3") = _
         ComboBoxLA.List(ComboBoxLA.ListIndex, 0)
         
'Selling Agent
Worksheets("Tempdb").Range("K3") = "NA"
Worksheets("FORM").Range("D4") = ComboBoxLA.List(ComboBoxLA.ListIndex, 0)
Worksheets("LR1").Range("D4") = ComboBoxLA.List(ComboBoxLA.ListIndex, 0)
Worksheets("LR2").Range("D4") = ComboBoxLA.List(ComboBoxLA.ListIndex, 0)
Worksheets("FORM").Range("D5") = "NA" ' Selling Agent
Worksheets("LR1").Range("D5") = "NA"  ' Selling Agent
Worksheets("LR2").Range("D5") = "NA"   ' Selling Agent
'///////To Misc Worksheet
Worksheets("Misc").Range("D8") = ComboBoxLA.List(ComboBoxLA.ListIndex, 0)
Worksheets("Misc").Range("D12") = ComboBoxLA.List(ComboBoxLA.ListIndex, 4) ' Franchise Fee %
Worksheets("Misc").Range("D13") = ComboBoxLA.List(ComboBoxLA.ListIndex, 2) ' Split Fee %
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Listing Associate ID
Worksheets("Tempdb").Range("E3") = _
      ComboBoxLA.List(ComboBoxLA.ListIndex, 1)
'////Selling Associate ID
Worksheets("Tempdb").Range("H3") = "0"
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Team Status
Worksheets("Tempdb").Range("AD3") = _
         ComboBoxLA.List(ComboBoxLA.ListIndex, 3)

'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////Transaction Credit
If OptionButtonOne Then
Worksheets("Tempdb").Range("C3") = "1"
Worksheets("Misc").Range("B48") = "1"

'///Selling Side
Worksheets("Tempdb").Range("F3") = "0"
ElseIf ThreeFourths Then
Worksheets("Tempdb").Range("C3") = ".75"
Worksheets("Misc").Range("B48") = ".75"
'///Selling Side
Worksheets("Tempdb").Range("F3") = "0"
ElseIf Onehalf Then
Worksheets("Tempdb").Range("C3") = ".50"
Worksheets("Misc").Range("B48") = ".50"
'///Selling Side
Worksheets("Tempdb").Range("F3") = "0"

Else
Worksheets("Tempdb").Range("C3") = ".25"
Worksheets("Misc").Range("B48") = "..25"
'///Selling Side
Worksheets("Tempdb").Range("F3") = "0"
 
End If
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Residential or Commercial
If Yes Then
Worksheets("Tempdb").Range("Z3") = "C"
Worksheets("Misc").Range("G9") = "C"
Else: Worksheets("Tempdb").Range("Z3") = "R"
Worksheets("Misc").Range("G9") = "R"
End If
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Income
'//////////////////////////////////////////////////////////////////////////////////////////////////////////
'//////////////////////  To  Tempdb
 Worksheets("Tempdb").Range("L3") = ComTextBox.Value
 Worksheets("Tempdb").Range("M3") = "0"  ' Listing Referral Income
 Worksheets("Tempdb").Range("N3") = BonusTextBox.Value
 Worksheets("Tempdb").Range("O3") = Other1IncTextBox1.Value
 Worksheets("Tempdb").Range("P3") = Other2IncTextBox2.Value
 
 '//////////  Selling side to Tempdb
 Worksheets("Tempdb").Range("R3") = "0"
 Worksheets("Tempdb").Range("S3") = "0"
 Worksheets("Tempdb").Range("T3") = "0"
 Worksheets("Tempdb").Range("U3") = "0"
 Worksheets("Tempdb").Range("V3") = "0"
 
  '//////////  Total Listing Commission to Tempdb
 Worksheets("Tempdb").Range("Q3") = Worksheets("Tempdb").Range("L3") + _
            Worksheets("Tempdb").Range("M3") + Worksheets("Tempdb").Range("N3") + _
            Worksheets("Tempdb").Range("O3") + Worksheets("Tempdb").Range("P3")
            
  '//////////  Total Selling Commission to Tempdb
 Worksheets("Tempdb").Range("W3") = Worksheets("Tempdb").Range("R3") + _
            Worksheets("Tempdb").Range("S3") + Worksheets("Tempdb").Range("T3") + _
            Worksheets("Tempdb").Range("U3") + Worksheets("Tempdb").Range("V3")
            
   '///////// Total Gross Commission (Listing + Selling
   
   Worksheets("Tempdb").Range("X3") = Worksheets("Tempdb").Range("Q3") + _
             Worksheets("Tempdb").Range("W3")
             
 '//////////////////////////////////////////////////////////////////////////////////////////////////////////
'//////////////////////  To  FORM
Worksheets("FORM").Range("C8") = ComTextBox.Value
 Worksheets("FORM").Range("C10") = BonusTextBox.Value
 Worksheets("FORM").Range("C13") = Other1IncTextBox1.Value
 Worksheets("FORM").Range("C14") = Other2IncTextBox2.Value
 
       
 
 
 '//////  Total Income FORM
 
 Worksheets("FORM").Range("C16") = Worksheets("FORM").Range("C8") + _
           Worksheets("FORM").Range("C9") + Worksheets("FORM").Range("C10") + _
           Worksheets("FORM").Range("C11") + Worksheets("FORM").Range("C12") + _
           Worksheets("FORM").Range("C13") + Worksheets("FORM").Range("C14")
 
  '//////////////////////////////////////////////////////////////////////////////////////////////////////////
'//////////////////////  To  Misc
 
Worksheets("Misc").Range("D3") = ComTextBox.Value
 Worksheets("Misc").Range("E3") = BonusTextBox.Value
 Worksheets("Misc").Range("F3") = Other1IncTextBox1.Value
 Worksheets("Misc").Range("G3") = Other2IncTextBox2.Value
 
 Worksheets("Misc").Range("E12") = Worksheets("Misc").Range("D3") + _
             Worksheets("Misc").Range("E3") + Worksheets("Misc").Range("F3") + _
              Worksheets("Misc").Range("G3")
              
         Worksheets("Misc").Range("E13") = Worksheets("Misc").Range("E12")
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Expense
'/////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////  Calculation of Franchise Fee and split fee
 
'////Franchise Fee
Worksheets("Misc").Range("C6") = Worksheets("Misc").Range("E12") _
* (Worksheets("Misc").Range("D12"))
Worksheets("FORM").Range("C24") = Worksheets("Misc").Range("C6")
Worksheets("Tempdb").Range("Y3") = Worksheets("Misc").Range("C6")
'///// Split Fee
Worksheets("Misc").Range("D6") = Worksheets("Misc").Range("E13") _
* (Worksheets("Misc").Range("D13"))
Worksheets("FORM").Range("C23") = Worksheets("Misc").Range("D6")

'////Internal Referral One
Worksheets("FORM").Range("C20") = IntRefoneTextBox.Value
Worksheets("Misc").Range("E6") = IntRefoneTextBox.Value
'////Internal Referral Two
Worksheets("FORM").Range("C21") = IntReftwoTextBox.Value
Worksheets("Misc").Range("F6") = IntReftwoTextBox.Value
'////  External Referral
Worksheets("FORM").Range("C22") = ExtreferralTextBox.Value
Worksheets("Misc").Range("G6") = ExtreferralTextBox.Value
'////  Other Expense One & two
Worksheets("FORM").Range("C26") = OthexponeTextBox.Value
Worksheets("Misc").Range("H6") = OthexponeTextBox.Value
Worksheets("FORM").Range("C27") = OthexptwoTextBox.Value
Worksheets("Misc").Range("D7") = OthexptwoTextBox.Value
'////  Agent Bill
Worksheets("FORM").Range("C19") = AgentBillTextBox.Value
'//////  Total Form Expense
 
 Worksheets("FORM").Range("C29") = Worksheets("FORM").Range("C19") + _
           Worksheets("FORM").Range("C20") + Worksheets("FORM").Range("C21") + _
           Worksheets("FORM").Range("C22") + Worksheets("FORM").Range("C23") + _
           Worksheets("FORM").Range("C24") + Worksheets("FORM").Range("C25") + _
           Worksheets("FORM").Range("C26") + Worksheets("FORM").Range("C27")
'////// ////////////////////////////////////////////////////////////////////////////////////////////////////
'//////////////////////////////////////////////////////////////////////////////////////////////////////////
'//////////////////   Net Amount to Agent on Form
Worksheets("FORM").Range("C32") = Worksheets("FORM").Range("C16") - _
Worksheets("FORM").Range("C29")
 
'///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////
'///////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////// Save or Not Save
'///////// No Internal Referrals
If Worksheets("Misc").Range("F43") = "0" And _
Worksheets("Misc").Range("G43") = "0" Then
If MsgBox("Save This Transaction?", vbYesNo) = vbYes Then
     Call Save
     Unload Me
    
     Else
     Unload Me
     ListA.Show
End If
'///////// One Internal Referral

ElseIf Worksheets("Misc").Range("F43") = "1" Then
    Unload Me
    InternalRefOne.Show

'///////// Two Internal Referrals

Else
   Unload Me
   InternalRefTwo.Show

End If
 

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Sheets("FORM").Visible = True

to avoid any doubt
 
Upvote 0
I Copied ?"< &"; Sheets(4).Name; "& >" in the immediate window and got the following.

Code: 
< &FORM& >

Thanks
 
Upvote 0
Thanks Mole999, but when I try" Sheets("FORM").Visible = True" I get the same error message on this line of code.

Thanks
 
Upvote 0
Try this
On Error Resume Next
Sheets("FORM").Visible = True

that way if already visible it will just carry on

it can be also set up to test for being visible and if not then make it so
 
Upvote 0
please confirm

this is your current code

Code: 
Option Explicit
'///// Date

Worksheets("FORM").Range("B4") = TextBox1.Value
''   Transfer Date to Database
'Private Sub DTPicker1_Click()
'ClosedateTextBox = DatePicker1.Value
'Sheets("DATABASE").Select
    'Range("A3").Select
 'Selection.End(xlDown).Select
 'ActiveCell.Offset(1, 0).Select
 'Selection = DTPicker1.Value

'End Sub
 
Upvote 0
The following is my code.

Code: 
Worksheets("FORM").Range("B4") = TextBox1.Value

I am testing the program on a vesion of excel that doesn't have a DTPicker.

Thanks
 
Upvote 0
then you very top line is outside of the SUBS
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,611
Messages
6,120,513
Members
448,967
Latest member
screechyboy79

Share this page

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
Back
Top