Sheet1 Not running

8700Rakf

New Member
Joined
May 15, 2019
Messages
32
So I have a lot of ComboBox on my sheet1, I cant seem to activate it execpt if I go to sheet2, and then back to Sheet1. Then they function as intended.

Is it possible to avoid this process, for it to function properly ???


Thanks in advance
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Any time you have problems with code it's best to show us all the code you have in your sheet.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
8700Rakf,

In order to assist you it would help us if you posted all of your macro code using code tags.

When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]
 

8700Rakf

New Member
Joined
May 15, 2019
Messages
32
Code:
Private Sub ClassBox_Change()



 Select Case VersionBox




 Case "OIML R51 1996 (Old EU)"
    Select Case ClassBox
        Case "Y(a)"
        Me.ScaleIntervalBox.AddItem "Single"
    End Select


 Case "OIML R51 2006 (New EU)"
    Select Case ClassBox
    Case "Y(a)"
        Me.ScaleIntervalBox.AddItem "Single"
        Me.ScaleIntervalBox.AddItem "Multi"


    Case "Y(b)"
        Me.ScaleIntervalBox.AddItem "Single"
    End Select


 Case "NMIA (AU)"
  Select Case ClassBox
        Case "Y(a)"
        Me.ScaleIntervalBox.AddItem "Single"
    End Select


 Case "NIST (US)"
  Select Case ClassBox
        Case "Class III"
        Me.ScaleIntervalBox.AddItem "Single"
    End Select
 End Select


End Sub






Private Sub Start_Click()


ActiveWorkbook.Sheets("Main Page").Activate


End Sub


Private Sub Reset_Click()


Me.ComboBox1.Clear
Me.ScaleIntervalBox.Clear
Me.ClassBox.Clear
Me.VersionBox.Clear
Me.TypeBox.Clear
Me.WeightBox.Clear
Me.UnitBox.Clear




End Sub


Private Sub Worksheet_Activate()
    Dim Versions As Variant
    Dim Typen As Variant
    Dim ScaleI As Variant
    Dim Unit As Variant
    Dim Weight As Variant
            
    
    Versions = Array("OIML R51 1996 (Old EU)", "OIML R51 2006 (New EU)", "NMIA (AU)", "NIST (US)")
    Typen = Array("Post Scale", "Scale")
    ScaleI = Array("Single", "Multi")
    Unit = Array("g", "kg", "Lb")
    Weight = Array(1000, 10000, 15000, 20000, 30000, 35000, 40000, 45000, 50000)
    
     With Me.VersionBox
        .Clear
        .List = Versions
     End With
     
     With Me.TypeBox
        .Clear
        .List = Typen
     End With
    


    
'    If ScaleIntervalBox = "OIML R51 1996 (Old EU)" And TypeBox = "Y(a)" Then
'     With Me.ScaleIntervalBox
'        .Clear
'        .List = ScaleI(1, 1)
'     End With
     
   
     With Me.UnitBox
        .Clear
        .List = Unit
     End With
    
     With Me.WeightBox
        .Clear
        .List = Weight
     End With


End Sub




Sub VersionBox_Change()
    Dim SelectionIndex As Integer
    Dim Dependentlist As Variant
    Dim SelectionIndex1 As Integer
    Dim Dependentlist1 As Variant


    Dependentlist = Array(Array("Y(a)"), _
                          Array("Y(a)", "Y(b)"), _
                          Array("Y(a)"), _
                          Array("Class III"))
                          




    SelectionIndex = Me.VersionBox.ListIndex
    With Me.ClassBox


        .Clear
        .Enabled = CBool(SelectionIndex <> -1)
    If .Enabled Then .List = Dependentlist(SelectionIndex) Else Me.VersionBox.Activate
    End With
    


    
End Sub


Sub ScaleIntervalBox_Change()
Select Case ScaleIntervalBox
        Case "Multi"
            Me.ComboBox1.AddItem "10/20"
            Me.ComboBox1.AddItem "20/50 V1"
            Me.ComboBox1.AddItem "20/50 V2"
End Select


    
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
You're code is in a Worksheet_Activate event, so it only runs when you activate the sheet.
How about putting it in the Workbook_Open event instead.
 

8700Rakf

New Member
Joined
May 15, 2019
Messages
32
When i do that, All the Me keywords dont work, and i am not sure How to change that
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
Just replace Me with the sheet name or sheet codename
 

8700Rakf

New Member
Joined
May 15, 2019
Messages
32
And what about the sub parts?

as fare as i know, subs dont work inside subs?
 

8700Rakf

New Member
Joined
May 15, 2019
Messages
32
This is what i've done, but the code dont work as it did before anymore.

Code:
Private Sub Workbook_Open()

'----------------Initialising-----------------------------------


    Dim Versions As Variant
    Dim Typen As Variant
    Dim ScaleI As Variant
    Dim Unit As Variant
    Dim Weight As Variant
    Dim SelectionIndex As Integer
    Dim Dependentlist As Variant
    
'-------------------List------------------------------------------
    
    Versions = Array("OIML R51 1996 (Old EU)", "OIML R51 2006 (New EU)", "NMIA (AU)", "NIST (US)")
    Typen = Array("Post Scale", "Scale")
    ScaleI = Array("Single", "Multi")
    Unit = Array("g", "kg", "Lb")
    Weight = Array(1000, 10000, 15000, 20000, 30000, 35000, 40000, 45000, 50000)
    
     With Worksheets("MainPage").VersionBox
        .Clear
        .List = Versions
     End With
     
     With Worksheets("MainPage").TypeBox
        .Clear
        .List = Typen
     End With
    




     With Worksheets("MainPage").UnitBox
        .Clear
        .List = Unit
     End With
    
     With Worksheets("MainPage").WeightBox
        .Clear
        .List = Weight
     End With
 
    
   
    Dependentlist = Array(Array("Y(a)"), _
                          Array("Y(a)", "Y(b)"), _
                          Array("Y(a)"), _
                          Array("Class III"))
                          




    SelectionIndex = Worksheets("MainPage").VersionBox.ListIndex
    With Worksheets("MainPage").ClassBox


        .Clear
        .Enabled = CBool(SelectionIndex <> -1)
    If .Enabled Then .List = Dependentlist(SelectionIndex) Else Worksheets("MainPage").VersionBox.Activate
    End With
    
'---------------- Case -----------------------------------------






Select Case VersionBox
Case "OIML R51 1996 (Old EU)"
 
 MainPage.ComboBox1.Clear
    Select Case ClassBox
        Case "Y(a)"
            Worksheets("MainPage").ComboBox1.Clear
            Worksheets("MainPage").ScaleIntervalBox.Clear
            Worksheets("MainPage").ScaleIntervalBox.AddItem "Single"
    End Select
 
Case "OIML R51 2006 (New EU)"
 
    Select Case ClassBox
        Case "Y(a)"
            Worksheets("MainPage").ScaleIntervalBox.Clear
            Worksheets("MainPage").ComboBox1.Clear
            Worksheets("MainPage").ScaleIntervalBox.AddItem "Single"
            Worksheets("MainPage").ScaleIntervalBox.AddItem "Multi"
        
        Select Case IntervalBox
            Case "Multi"
                Worksheets("MainPage").ComboBox1.Clear
                Worksheets("MainPage").ComboBox1.AddItem "10/20"
                Worksheets("MainPage").ComboBox1.AddItem "20/50 V1"
                Worksheets("MainPage").ComboBox1.AddItem "20/50 V2"
            End Select
        
    
        Case "Y(b)"
            Worksheets("MainPage").ScaleIntervalBox.Clear
            Worksheets("MainPage").ScaleIntervalBox.AddItem "Single"
    End Select
    
Case "NMIA (AU)"
    Select Case ClassBox
  
        Case "Y(a)"
            Worksheets("MainPage").ScaleIntervalBox.Clear
            Worksheets("MainPage").ScaleIntervalBox.AddItem "Single"
    End Select
 
Case "NIST (US)"
  Select Case ClassBox
  
        Case "Class III"
            Worksheets("MainPage").ScaleIntervalBox.Clear
            Worksheets("MainPage").ScaleIntervalBox.AddItem "Single"
    End Select
 End Select
 
 
            


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,753
Messages
5,470,571
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top