I have been using this excel program for several months without issues. suddenly a couple days ago it started to throw this error. On sheet named «Input» I will double click a cell in column «A» which will create a drop down box that will fill with data from the «Data» sheet. I start typing and then I select the data to add to the cell. Now when I click the cell and get an error message «Compile Error — Method or data member not found». Here is my block of code and the error is showing near the bottom highlighting «Me.TempCombo.Activate».
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
If Target.Column = 1 And Target.Row > 12 And Target.Row <> HRRow And Target.Row <> HRRow - 1 Then
lRow = Sheets("Data").Range("A65536").End(xlUp).Row
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
'If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Cancel = True
Application.EnableEvents = False
'get the data validation formula
'str = Target.Validation.Formula1
'str = Right(str, Len(str) - 1)
str = "=Data!A2:A" & lRow
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
'cboTemp.Activate
Me.TempCombo.Activate
'open the drop down list automatically
Me.TempCombo.DropDown
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
I tried several things and for the life of me I cannot figure out what changed.
Any help will be appreciated. Thank you.
Kritner
13.5k10 gold badges46 silver badges72 bronze badges
asked Dec 10, 2014 at 14:26
2
I ran into the same error and was able to solve it as Rory suggested. I searched my machine for *.exd files and found a few. The issue was solved for me after removing C:Users<username>AppDataLocalTempExcel8.0MSForms.exd
…the others seemed to be unrelated to the ActiveX controls in Excel.
answered Dec 29, 2014 at 21:13
Looks like the code came from an example like this: http://www.contextures.com/xlDataVal10.html
except your code has commented out the line which activates the cboTemp combobox. Your code is attempting to access the TempCombo attribute of the worksheet (which I don’t think exists). Uncomment 'cboTemp.Activate
on the line above the highlighted error line.
answered Dec 10, 2014 at 14:39
LeftyLefty
4263 silver badges10 bronze badges
I had the same problem, my code broke this morning. Fortunately, I recalled that I ran Windows Update this weekend. I performend a system restore (earliest available restore point was 8th of december), and now the problem is gone.
I never did understand the panicy server guys who were always making backups and spending a whole lot of time testing before/after system updates, in all my years I never experienced any problems. Now I sure figured out what they were talking about. Lesson learnt. I’ll try running win update again in a few months, hopefully MS has solved the problem by then.
Best of luck
answered Dec 15, 2014 at 19:18
1
Permalink
Cannot retrieve contributors at this time
description | title | ms.date | f1_keywords | ms.assetid |
---|---|---|---|---|
Learn more about: Method or data member not found |
Method or data member not found |
07/20/2015 |
vbrID461 |
40b178c3-7dc4-4216-8460-17ff8d9aedf1 |
The method or data member you have tried to access was not found.
To correct this error
- Make sure you did not misspell the name of the method or data member.
See also
- Error Types
You had the code in a code module and a worksheet code module. If the code is in the worksheet module you can use syntax like:
Me.ListBox2.Value = ""
If you use a code module you have to specify the worksheet:
Sheets("Font Book").ListBox2.Value = ""
It looks like your code was duplicative. I removed the code module stuff and now it works fine (See attached)
- Remove From My Forums
-
Question
-
Hello I’m getting the error message «Method or data member not found». This happens with a form that I’m working on in Access. The form is used to search the database for records matching a name that is entered into a text box. Here is the code in VBA if
it is any help:Option Compare Database
Private Sub RefreshSearch()
Me.chkDoSearch.Value = True
Me.FarmerSearchbyName_subform.Requery
Me.FarmerSearchbyName_subform.SetFocus
End Sub
Private Sub btnNewSearch_Click()
With Me.txtLookup
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
End SubPrivate Sub btnOk_Click()
If Me.FarmerSearchbyName_subform.CurrentRecord > 0 Then <————— This is where the error message is generated
Me.Tag = Me.FarmerSearchbyName_subform.Form.Recordset(«FarmerID»)
Me.Visible = False
End SubPrivate Sub btnSearch_Click()
RefreshSearch
End SubPrivate Sub chkIncludeInactive_AfterUpdate()
RefreshSearch
End Sub
Private Sub Form_Activate()
Me.Tag = «»
End SubPrivate Sub Form_Load()
Me.Tag = «»
End Sub
fafelbko Пользователь Сообщений: 9 |
#1 06.09.2018 12:14:43 Добрый день коллеги.
|
||
StoTisteg Пользователь Сообщений: 441 |
#2 06.09.2018 12:20:39
Ячейки должны быть с того же листа, что и включающий их диапазон. Изменено: StoTisteg — 06.09.2018 12:20:46 |
||
fafelbko Пользователь Сообщений: 9 |
Внёс ваше исправление, но точно такая же ошибка «Method or data member not found» и ругается теперь на .Cells |
ivanok_v2 Пользователь Сообщений: 712 |
#4 06.09.2018 12:31:04
у вас точно так название? |
||
StoTisteg Пользователь Сообщений: 441 |
А переменная Sheet1 у вас вообще какого типа? И где ей присваивается значение? |
Hugo Пользователь Сообщений: 23373 |
Подозреваю что нет объекта Sheet1. Файла нет (да и всего кода нет), поэтому остаётся только подозревать. |
StoTisteg Пользователь Сообщений: 441 |
Hugo, вот и я подозреваю, что её нет и имеется в виду Worksheets(«Sheet1»)… |
StoTisteg Пользователь Сообщений: 441 |
А ещё я в упор не понимаю, нафига нужен ВПР там, где работают Find и Offset… Изменено: StoTisteg — 06.09.2018 12:58:36 |
fafelbko Пользователь Сообщений: 9 |
#9 06.09.2018 12:46:02 Коллеги, прошу прощения, не дописал.
Ругается Type mismatch Прикрепленные файлы
|
||
ivanok_v2 Пользователь Сообщений: 712 |
#10 06.09.2018 12:48:42
fafelbko, у вас есть понятия работы кода? |
||
fafelbko Пользователь Сообщений: 9 |
Не совсем. Не могли бы подсказать? |
StoTisteg Пользователь Сообщений: 441 |
fafelbko, знаете, Вам нужно взять справочник и почитать там, что такое переменная, что означает Worksheets(«Sheet1») и понять, почему написанное Вами — мягко говоря, ерунда. |
Юрий М Модератор Сообщений: 60773 Контакты см. в профиле |
#13 06.09.2018 13:09:53 fafelbko, весь код не смотрел, но переменную Вы объявляете неправильно. Нужно примерно так:
|
||
_Boroda_ Пользователь Сообщений: 1496 Контакты см. в профиле |
#14 06.09.2018 13:14:39
Изменено: _Boroda_ — 06.09.2018 13:14:45 Скажи мне, кудесник, любимец ба’гов… |
||
fafelbko Пользователь Сообщений: 9 |
#15 06.09.2018 14:05:38 Коллеги. Прошу простить мою некомпетентность. |