Sheet1 Not running

[8700Rakf]

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

 

[My Aswer Is This]

Any time you have problems with code it's best to show us all the code you have in your sheet.

 

[hiker95]

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]

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

 

[8700Rakf]

Bump

 

[Fluff]

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]

When i do that, All the Me keywords dont work, and i am not sure How to change that

 

[Fluff]

Just replace Me with the sheet name or sheet codename

 

[8700Rakf]

And what about the sub parts?

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

 

[8700Rakf]

This is what i've done, but the code dont work as it did before anymore.

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