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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Any time you have problems with code it's best to show us all the code you have in your sheet.
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Just replace Me with the sheet name or sheet codename
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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