none
Query RRS feed

  • Question

  • Hi, I am new to VBA. I was trying to develop a form with ComboBox. I am having 7 ComboBoxes in my user form. The comboboxes are working fine everywhere. But, there is a small problem. I am having a Clear Data command button to delete data from some specific cells in the excel. The code for deletion is given below.

    Private Sub Delete_Click()
    ' Delete Macro
        Sheets("Report_Bending").Select
        Range("B15,B25,E45,B105").Select
        Selection.ClearContents
        Range("B8").Select
        Sheets("Major_axis_bending").Select
        Range("B9,B35").Select
        Selection.ClearContents
        Range("B36").Select
        Selection.ClearContents
        Sheets("Report_Bending").Select
        Range("B8").Select
    End Sub

    Now here comes the problem. Once I have run this code for deletion, two of the comboboxes no longer work and it returns the error "RunTime Error 13" Type Mismatch.  Below is the code for those two comboboxes.

    ---------------------------------------------------------

    Private Sub TorRes_Click()
        Sheets("Major_axis_bending").Activate
        Range("B35").Value = TorRes.Value
    End Sub

    Private Sub TorRes_DropButtonClick()
        If Range("B35") = "" And TorRes.Text = "Select" Then
            TorRes.AddItem ("Fully restrained")
            TorRes.AddItem ("Partially restrained by bottom flange support connection")
            TorRes.AddItem ("Partially restrained by bottom flange bearing support")
            TorRes.Text = ""
        End If
    End Sub

    --------------------------------------------------------

    Private Sub WarRes_Click()
        Sheets("Major_axis_bending").Activate
        Range("B36").Value = WarRes.Value
    End Sub

    Private Sub WarRes_DropButtonClick()
        If Range("B36") = "" And WarRes.Text = "Select" Then
            WarRes.AddItem ("Both flanges partially restrained")
            WarRes.AddItem ("Compression flange fully restrained")
            WarRes.AddItem ("Both flanges fully restrained")
            WarRes.AddItem ("Compression flange partially restrained")
            WarRes.AddItem ("Warping not restrained in both flanges")
            WarRes.Text = ""
        End If
    End Sub

    ------------------------------------------------------------

    The other comboboxes are having similar codes but they don't show any error. One such macro for non erroneous combobox is specified below.

    Private Sub Lateral_Click()
        Sheets("Report_Bending").Activate
        Range("B105").Value = Lateral.Value
        If Lateral.Value = "fully supported" Then
            Frame1.Visible = False
        Else
            Frame1.Visible = True
        End If
    End Sub

    Private Sub Lateral_DropButtonClick()
        If Range("B105") = "" And Lateral.Text = "Select" Then
            Lateral.AddItem ("fully supported")
            Lateral.AddItem ("supported at intervals")
            Lateral.AddItem ("unsupported")
            Lateral.Text = ""
        End If
    End Sub

    ------------------------------------------------------------------------------

    Further, if the excel VBA is left idle for sometime, microsoft excel 2007 crashes automatically and restarts. Please help. 

    Sunday, September 15, 2013 4:40 PM

Answers

  • Perhaps this is the code you need to achieve what you want to:

    Private Sub TorRes_Click()
        Sheets("Major_axis_bending").Range("B35").Value = TorRes.Value
    End Sub

    Private Sub UserForm_Initialize()
        TorRes.AddItem ("Fully restrained")
        TorRes.AddItem ("Partially restrained by bottom flange support connection")
        TorRes.AddItem ("Partially restrained by bottom flange bearing support")
       
        WarRes.AddItem ("Both flanges partially restrained")
        WarRes.AddItem ("Compression flange fully restrained")
        WarRes.AddItem ("Both flanges fully restrained")
        WarRes.AddItem ("Compression flange partially restrained")
        WarRes.AddItem ("Warping not restrained in both flanges")
    End Sub

    Private Sub WarRes_Click()
        Sheets("Major_axis_bending").Range("B36").Value = WarRes.Value
    End Sub

    When opening the userform, the comboboxes will be filled with a list data.

    You don't need to delete anything.

     Jan


    • Edited by jgkzdl Tuesday, September 17, 2013 9:04 PM
    • Marked as answer by Tupai150913 Wednesday, September 18, 2013 4:09 PM
    Tuesday, September 17, 2013 9:03 PM

All replies

  • I think you have to try at first to avoid too many selections.

    I'm not sure if it will help (did not test it) but the next code is a little bit cleaner:

    Private Sub Delete_Click()
     ' Delete Macro

         Sheets("Report_Bending").Range("B15,B25,E45,B105").ClearContents
         Sheets("Major_axis_bending").Range("B9,B35").ClearContents
         Range("B36").ClearContents
     End Sub

    Private Sub TorRes_Click()
         Sheets("Major_axis_bending").Range("B35").Value = TorRes.Value
     End Sub

    Private Sub TorRes_DropButtonClick()
         If Sheets("Major_axis_bending").Range("B35") = "" And TorRes.Text = "Select" Then
             TorRes.AddItem ("Fully restrained")
             TorRes.AddItem ("Partially restrained by bottom flange support connection")
             TorRes.AddItem ("Partially restrained by bottom flange bearing support")
             TorRes.Text = ""
         End If
    End Sub

    Private Sub WarRes_Click()
         Sheets("Major_axis_bending").Range("B36").Value = WarRes.Value
     End Sub

     Private Sub WarRes_DropButtonClick()
         If Sheets("Major_axis_bending").Range("B36") = "" And WarRes.Text = "Select" Then
             WarRes.AddItem ("Both flanges partially restrained")
             WarRes.AddItem ("Compression flange fully restrained")
             WarRes.AddItem ("Both flanges fully restrained")
             WarRes.AddItem ("Compression flange partially restrained")
             WarRes.AddItem ("Warping not restrained in both flanges")
             WarRes.Text = ""
         End If
     End Sub

     Jan

    Monday, September 16, 2013 2:33 PM
  • No.It did not work. It now says "Run time error 1004:Select method of range class failed".The debugger points to the delete macro after that.

    

    Monday, September 16, 2013 4:27 PM
  • Further, the next error ""RunTime Error 13" Type Mismatch still remains even with the updated code.
    Monday, September 16, 2013 4:32 PM
  • I tested the code in an empty workbook with only the sheets "Major_axis_bending" and "Report_Bending".

    A form with the two comboboxes "TorRes" and "WarRes" from which the property 'Text' I have set to 'Select'.

    It worked without any error.

    So, I don't know what else could be the course of your problems, but not this code on its own.

    The line Range("B36").ClearContents is probably the course of the 1004 error.

    This line had to be: Sheets("Major_axis_bending").Range("B36").ClearContents

    Jan

    Tuesday, September 17, 2013 9:47 AM
  • With this also, the main problem still remains. After running the delete macro, these two combo-boxes are still showing run time error 13. Is there any other way to create  a combo box? May be that will help. Because in my current code, if I am not clearing data from the cells to which the combo-boxes are linked, the drop down does not show any text after running the user form. This is the reason why I am clearing data using the delete macro.
    Tuesday, September 17, 2013 6:01 PM
  • Perhaps this is the code you need to achieve what you want to:

    Private Sub TorRes_Click()
        Sheets("Major_axis_bending").Range("B35").Value = TorRes.Value
    End Sub

    Private Sub UserForm_Initialize()
        TorRes.AddItem ("Fully restrained")
        TorRes.AddItem ("Partially restrained by bottom flange support connection")
        TorRes.AddItem ("Partially restrained by bottom flange bearing support")
       
        WarRes.AddItem ("Both flanges partially restrained")
        WarRes.AddItem ("Compression flange fully restrained")
        WarRes.AddItem ("Both flanges fully restrained")
        WarRes.AddItem ("Compression flange partially restrained")
        WarRes.AddItem ("Warping not restrained in both flanges")
    End Sub

    Private Sub WarRes_Click()
        Sheets("Major_axis_bending").Range("B36").Value = WarRes.Value
    End Sub

    When opening the userform, the comboboxes will be filled with a list data.

    You don't need to delete anything.

     Jan


    • Edited by jgkzdl Tuesday, September 17, 2013 9:04 PM
    • Marked as answer by Tupai150913 Wednesday, September 18, 2013 4:09 PM
    Tuesday, September 17, 2013 9:03 PM
  • Thanks a lot. It solved all of my problems.
    Wednesday, September 18, 2013 4:09 PM
  • Dear  Jan,

    In continuation to the above query, I now have one more question.  What I want to achieve is if from the dropdown of TorRes "Fully restrained" is selected, the dropdown for WarRes combo box will show the four items

    "Both flanges partially restrained"
    "Compression flange fully restrained"
    "Both flanges fully restrained"
    "Compression flange partially restrained"

    and if any other value is selected from the dropdown of TorRes, then dropdown of WarRes will show

    "Warping not restrained in both flanges"

    I have written the code as below. But, it does not seem to work.

     'Torsion
        TorRes.AddItem ("Fully restrained")
        TorRes.AddItem ("Partially restrained by bottom flange support connection")
        TorRes.AddItem ("Partially restrained by bottom flange bearing support")
        'Warping
        If TorRes.Value = "Fully restrained" Then
            WarRes.AddItem ("Both flanges partially restrained")
            WarRes.AddItem ("Compression flange fully restrained")
            WarRes.AddItem ("Both flanges fully restrained")
            WarRes.AddItem ("Compression flange partially restrained")
        Else
            WarRes.AddItem ("Warping not restrained in both flanges")
        End If

    Wednesday, September 18, 2013 6:01 PM
  • This code works for me, and for UserForm_Initialize it is the same as yours.

    But I have add the code for the AfterUpdate event from combobox Torres.

    Private Sub TorRes_AfterUpdate()
        Me.WarRes.RowSource = ""
        If Me.TorRes = "Fully restrained" Then
            WarRes.AddItem ("Both flanges partially restrained")
            WarRes.AddItem ("Compression flange fully restrained")
            WarRes.AddItem ("Both flanges fully restrained")
            WarRes.AddItem ("Compression flange partially restrained")
        Else
            WarRes.AddItem ("Warping not restrained in both flanges")
       End If
    End Sub
    
    Private Sub UserForm_Initialize()
        TorRes.AddItem ("Fully restrained")
        TorRes.AddItem ("Partially restrained by bottom flange support connection")
        TorRes.AddItem ("Partially restrained by bottom flange bearing support")
    
        If Me.TorRes.Value = "Fully restrained" Then
            WarRes.AddItem ("Both flanges partially restrained")
            WarRes.AddItem ("Compression flange fully restrained")
            WarRes.AddItem ("Both flanges fully restrained")
            WarRes.AddItem ("Compression flange partially restrained")
        Else
            WarRes.AddItem ("Warping not restrained in both flanges")
       End If
    End Sub
    

    Jan
    Wednesday, September 18, 2013 9:10 PM
  • But this has a small problem. Suppose you have selected "Fully restrained" from dropdown of TorRes and selected say "Both flanges partially restrained from dropdown of WarRes". Now, suppose again if you want to change TorRes to "Partially restrained by bottom flange support connection" instead and then again if you click on WarRes dropdown, you will see that the items for WarRes from previous case ramain and for the case of "Both flanges partially restrained " the WarRes criteria is appended at the end of the dropdown list. Hence, if you go on updating TorRes for more than once, the dropdown for WarRes goes on getting appended to the list continuously.

    Thursday, September 19, 2013 6:35 AM
  • I used the following code instead. Its working. I removed WarRes.AddItem from UserForm_Initialize() part.

    Private Sub UserForm_Initialize()
        'Torsion
        TorRes.AddItem ("Fully restrained")
        TorRes.AddItem ("Partially restrained by bottom flange support connection")
        TorRes.AddItem ("Partially restrained by bottom flange bearing support")
    End Sub

    Private Sub TorRes_AfterUpdate()
        WarRes.Clear
        If Me.TorRes = "Fully restrained" Then
            WarRes.AddItem ("Both flanges partially restrained")
            WarRes.AddItem ("Compression flange fully restrained")
            WarRes.AddItem ("Both flanges fully restrained")
            WarRes.AddItem ("Compression flange partially restrained")
        Else
            WarRes.AddItem ("Warping not restrained in both flanges")
       End If
    End Sub

    Thursday, September 19, 2013 7:02 AM
  • That is why I used  Me.WarRes.RowSource = ""
    at the start of
    TorRes_AfterUpdate and that worked (for me?).

    But even better is using WarRes.Clear as you do.

    Jan

    Thursday, September 19, 2013 8:04 AM