• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combobox error - Compiler error

Manish Sharma

Manish Sharma

Member
Hi Friends,

I have a VBA with ActiveX combobox which is giving error while opening the workbook. Error message is "Compiler error:Method or data member not found".

Any anyone you please tell me what is the mistake i am doing in the coding. Below is the coding for the three comboboxes

Code: 
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Application.EnableEvents = False
With ThisWorkbook.Sheets("Tool")
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
End With
Range("XFB5").Value = ""
Application.EnableEvents = False
End Sub

Private Sub ComboBox2_Change()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Tool")
Me.ComboBox3.Value = ""
End With
Range("XFB5").Value = ""
End Sub

Private Sub ComboBox3_Change()
Application.ScreenUpdating = False

    With ThisWorkbook.Sheets
        If Sheet1.Range("XFA4").Value = "Greater than" Then
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = False
            Sheet1.Txtbx7.Visible = False
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "Less than" Then
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = False
            Sheet1.Txtbx7.Visible = False
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "Between" Then
            Sheet1.Txtbx7.Visible = True
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = True
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "" Then
            Sheet1.Txtbx7.Visible = False
            Sheet1.Txtbx5.Visible = False
            Sheet1.Txtbx6.Visible = False
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        End If
    End With
        Range("XFB5").Value = ""
End Sub

Thanks & Regards,
Manish
 
Sorry i missed it Combobox1 &2 are getting populated by VBA code which is below.

Code: 
Private Sub Worksheet_Activate()
With ThisWorkbook.Sheets
Dim i As Integer
i = Sheet1.Range("$XEU$2").Value
ComboBox1.ListFillRange = "=$XET$2:$XET$" & i

Dim j As Integer
j = Sheet1.Range("$XEW$2").Value
ComboBox2.ListFillRange = "=$XEV$2:$XEV$" & j
End With
End Sub

And i am getting error message in "Me.ComboBox2.Value = " line

Regards,
Manish
Manish Sharma said:
Hi Friends,

I have a VBA with ActiveX combobox which is giving error while opening the workbook. Error message is "Compiler error:Method or data member not found".

Any anyone you please tell me what is the mistake i am doing in the coding. Below is the coding for the three comboboxes

Code: 
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Application.EnableEvents = False
With ThisWorkbook.Sheets("Tool")
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
End With
Range("XFB5").Value = ""
Application.EnableEvents = False
End Sub

Private Sub ComboBox2_Change()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Tool")
Me.ComboBox3.Value = ""
End With
Range("XFB5").Value = ""
End Sub

Private Sub ComboBox3_Change()
Application.ScreenUpdating = False

    With ThisWorkbook.Sheets
        If Sheet1.Range("XFA4").Value = "Greater than" Then
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = False
            Sheet1.Txtbx7.Visible = False
          
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "Less than" Then
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = False
            Sheet1.Txtbx7.Visible = False
          
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "Between" Then
            Sheet1.Txtbx7.Visible = True
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = True
          
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "" Then
            Sheet1.Txtbx7.Visible = False
            Sheet1.Txtbx5.Visible = False
            Sheet1.Txtbx6.Visible = False
          
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        End If
    End With
        Range("XFB5").Value = ""
End Sub

Thanks & Regards,
Manish
Click to expand...
 
Hi Manish ,

Can you clarify the following ?

1. On which sheet are the comboboxes ?

2. Going through your code , you have sometimes used the Sheet1. notation , sometimes you have used Sheets("Tool") notation ; please do not mix it up like this , unless there is a real requirement for this kind of coding.

3. You say that the error occurs when you open the workbook , but you have not posted any Workbook_Open macro ; you have a Worksheet_Activate macro ; is this the one which has the comboboxes on it ?

When you have a question like this , the ideal thing to do would be to upload the workbook.

Narayan
 
NARAYANK991 said:
Hi Manish ,

Can you clarify the following ?

1. On which sheet are the comboboxes ?

2. Going through your code , you have sometimes used the Sheet1. notation , sometimes you have used Sheets("Tool") notation ; please do not mix it up like this , unless there is a real requirement for this kind of coding.

3. You say that the error occurs when you open the workbook , but you have not posted any Workbook_Open macro ; you have a Worksheet_Activate macro ; is this the one which has the comboboxes on it ?

When you have a question like this , the ideal thing to do would be to upload the workbook.

Narayan
Click to expand...


Hi Narayan,
1. Comboxes are on Sheet1 with sheet name as Tool
2. I will clear the mix up
3. There is no workbook open macro, in the file. but then also i am getting this error after opening the workbook.
Yes, the sheet having the workbook_Activate macro is the one where the comboboxes are placed.

Yes, i can understand, but my file have some office data, which i cant make it public. Hope you can understand my situation.
 
Hi Manish ,

You can always clear the worksheet of all data , and then upload it ; the problem you are facing is not dependent on the data.

I tried reproducing your problem , but I am not able to do so.

Hopefully someone else will be able to help.

In the meantime , I suggest you go through the following links :

http://xlvba.fr.yuku.com/topic/318/Using-WithEnd-With-statements#.Uyp-T-OSyuI


Your usage of the following code :
Code: 
With ThisWorkbook.Sheets("Tool")
     Me.ComboBox2.Value = ""
     Me.ComboBox3.Value = ""
End With
is an indication that you need to do some reading.

Narayan
 
Hi Narayan,

I have discovered, why my code is giving error. But i dont know how to resolve it.

Below is the code which i am using Sheet1
Code: 
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Application.EnableEvents = False
ComboBox2.Value = ""
ComboBox3.Value = ""
Range("XFB5").Value = ""
Application.EnableEvents = False
End Sub

Private Sub ComboBox2_Change()
Application.ScreenUpdating = False
ComboBox3.Value = ""
Range("XFB5").Value = ""
End Sub

Private Sub ComboBox3_Change()
Application.ScreenUpdating = False

    With ThisWorkbook.Sheets
        If Sheet1.Range("XFA4").Value = "Greater than" Then
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = False
            Sheet1.Txtbx7.Visible = False
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "Less than" Then
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = False
            Sheet1.Txtbx7.Visible = False
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "Between" Then
            Sheet1.Txtbx7.Visible = True
            Sheet1.Txtbx5.Visible = True
            Sheet1.Txtbx6.Visible = True
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        ElseIf Sheet1.Range("XFA4").Value = "" Then
            Sheet1.Txtbx7.Visible = False
            Sheet1.Txtbx5.Visible = False
            Sheet1.Txtbx6.Visible = False
           
            Range("XFB4").Value = ""
            Range("XFC4").Value = ""
        End If
    End With
        Range("XFB5").Value = ""
End Sub

Private Sub Worksheet_Activate()
With ThisWorkbook.Sheets
Dim i As Integer
i = Sheet1.Range("$XEU$2").Value
ComboBox1.ListFillRange = "=$XET$2:$XET$" & i

Dim j As Integer
j = Sheet1.Range("$XEW$2").Value
ComboBox2.ListFillRange = "=$XEV$2:$XEV$" & j
End With
End Sub



'*********************************************************************
'This is for restricting the text boxes to enter only numeric value
'*********************************************************************

Private Sub Txtbx5_Change()
'This is for sales criteria inour value 1
Application.ScreenUpdating = False
    ans = check_input("Txtbx5", "XF43") ' pass the control name and the cell  where the data needs to be displayed.

End Sub

Private Sub Txtbx7_Change()
'This is for sales criteria inour value 2
Application.ScreenUpdating = False
    ans = check_input("Txtbx7", "XFC4") ' pass the control name and the cell  where the data needs to be displayed.

End Sub

Private Sub Txtbx8_Change()
'This is for sales criteria inour value 2
Application.ScreenUpdating = False
    ans = check_input("Txtbx8", "XFB5") ' pass the control name and the cell  where the data needs to be displayed.

End Sub

Function check_input(Tb As String, rng As String) As String

   Dim wrk As Worksheet
    Dim OLEObj As OLEObject
    'set it to the sheet that has the textboxes
    Set wks = Worksheets("Tool")

    For Each OLEObj In wks.OLEObjects
        If TypeOf OLEObj.Object Is MSForms.TextBox Then
            'check if the object name matches and check if value is numeric
            If UCase(OLEObj.Name) = UCase(Tb) And IsNumeric(OLEObj.Object.Text) = False And OLEObj.Object.Text <> "" Then
            MsgBox "Enter Numeric Value", vbExclamation
            OLEObj.Object.Text = ""
            Exit Function
            End If
            'put the textbox value in the cell that is passed to  this function
            If UCase(OLEObj.Name) = UCase(Tb) Then
            wks.Range(rng) = Val(OLEObj.Object.Text)
            Exit Function
            End If
           
           
        End If
    Next OLEObj
End Function

It is working when style property of combobox is set as 0, but it gives me error when it is changed as 2. and the error which i am getting is Run-Time error 424: object required.

Can you please help me with, and sorry for the earlier confusion.

NARAYANK991 said:
Hi Manish ,

You can always clear the worksheet of all data , and then upload it ; the problem you are facing is not dependent on the data.

I tried reproducing your problem , but I am not able to do so.

Hopefully someone else will be able to help.

In the meantime , I suggest you go through the following links :

http://xlvba.fr.yuku.com/topic/318/Using-WithEnd-With-statements#.Uyp-T-OSyuI


Your usage of the following code :
Code: 
With ThisWorkbook.Sheets("Tool")
     Me.ComboBox2.Value = ""
     Me.ComboBox3.Value = ""
End With
is an indication that you need to do some reading.

Narayan
Click to expand...
 
You must log in or register to reply here.
Back
Top