Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Sheet1 Not running

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sheet1 Not running

    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

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,478
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Sheet1 Not running

    Any time you have problems with code it's best to show us all the code you have in your sheet.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,643
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Sheet1 Not running

    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]

    Paste your code here.

    [/code]
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sheet1 Not running

    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

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sheet1 Not running

    Bump

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Sheet1 Not running

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sheet1 Not running

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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Sheet1 Not running

    Just replace Me with the sheet name or sheet codename
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sheet1 Not running

    And what about the sub parts?

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

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sheet1 Not running

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •