Compile error: Method or data member not found

[nicktayl]

Hi,

I have an ActiveX text box in my Excel document. I'm trying to restrict the data type to numbers, so I added code. Even after making sure that the required References were added (Microsoft ActiveX Data Objects 6.1 Library), I'm still receiving the following error message: "Compile error: Method or data member not found". I have Microsoft Office 2016 32-bit and Windows 2007.

On a separate note, it seems that ActiveX controls are not the best. Is there a better way to creating forms for Excel models?

Private Sub txt_targeted_profit_margin_Change()

OnlyNumbers2


End Sub
Private Sub OnlyNumbers2()
If Not Me.ActiveControl Is Nothing Then
  MessageBox.Show (Me.ActiveControl.Name)
End If
End Sub

Thank You,
Nick

 

[JoeMo]

Your syntax looks strange.

Active-X controls are OLE objects. If your text box is on the activesheet, assuming you have not changed its name from the name Excel gave it when you inserted it, this will produce its name and give you an idea of how to work with it.

Sub nick()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
    If obj.Name Like "TextBox*" Then
        MsgBox obj.Name
    End If
Next obj
End Sub

 

[Norie]

Nick

ActiveControl is for controls on userforms, not controls on sheets.

What's the name of your textbox?

 

[nicktayl]

Thank you! Very helpful. I fixed it with the below:

Private Sub txt_targeted_profit_margin_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case Asc("0") To Asc("9")
            ' Allow digits
        Case Asc("-")
            ' Allow only one negative sign at the first position
            If InStr(1, Me.txt_targeted_profit_margin.Text, "-") > 0 Or Me.txt_targeted_profit_margin.SelStart > 0 Then
                KeyAscii = 0
            End If
        Case Asc(".")
            ' Allow only one decimal point
            If InStr(1, Me.txt_targeted_profit_margin.Text, ".") > 0 Then
                KeyAscii = 0
            End If
        Case Else
            KeyAscii = 0
    End Select
End Sub

Now to figure out why the value in the linked cell is stored as text and how to right align. I'm used to MS Access controls. Given that, I assumed using controls on a worksheet would be more intuitive.

Thanks!
Nick