Compile Error: Method of data member not found (VBA)

**vbatech** · 03-06-2012, 06:59 PM

Hi,

I created text boxes in 2 files, source_file.xlsm and destination_file.xlsm with the names TextBox1 and destTextBox respectively. I keep getting this error and not sure how to fix it. Can someone please help? I tried searching for similar issues but couldn't find anything that was specific to my issue.

Compile Error: Method or data member not found.

The goal is to copy the text in TextBox1 (from the source_file.xlsm) to DestTextBox (from destination_file.xlsm)
I created the text box from Insert > Text Box

Below is the code:

Please Login or Register  to view this content.

Below is a screenshot of the References I have set up:
References - VBAProject.jpg

Thanks in advance for your help.

**marreco** · 03-06-2012, 07:02 PM

Read the forum rules!
Place the tags around the code

**vbatech** · 03-06-2012, 07:33 PM

Sorry about that, I have revised it to follow the forum rules.

**vbatech** · 03-06-2012, 08:05 PM

Hi Rory,

Thanks for the help. I tried running the code and got this error
Run-time error '438': Object doesn't support this property or method

I also tried the following code and it doesn't work:

Please Login or Register  to view this content.

**vbatech** · 03-07-2012, 01:13 AM

If anyone else has some thoughts, that would be helpful.

**OnErrorGoto0** · 03-07-2012, 04:04 AM

If you get a runtime error then either you did not use ActiveX textboxes or you are looking at the wrong sheet. For the former, use

Please Login or Register  to view this content.

**Firefly2012** · 03-07-2012, 04:56 AM

Originally Posted by OnErrorGoto0

The worksheet class does not have those text boxes. Declare both worksheet variables as Object instead. Note that you currently have both variables assigned to the workbook containing the code. I think you mean:

Please Login or Register  to view this content.

That was very interesting. Didn't realise that would work. Nice!

**OnErrorGoto0** · 03-07-2012, 05:02 AM

Strictly speaking I should have said "The Worksheet interface does not implement those members." rather than "The worksheet class does not have those text boxes." but the net effect is the same.

**Firefly2012** · 03-07-2012, 05:49 AM

Originally Posted by OnErrorGoto0

Strictly speaking I should have said "The Worksheet interface does not implement those members." rather than "The worksheet class does not have those text boxes."

I was just about to point out your oversight, but then I saw you had corrected yourself

**OnErrorGoto0** · 03-07-2012, 06:56 AM

Yep - I knew someone would point it out if I didn't mention it...

**vbatech** · 03-07-2012, 12:02 PM

Hi all, thanks for your response.

I used the code that was suggested above and I am still getting Run-time error '438': Object doesn't support this property or method.

Not sure why this keeps giving me this error message. In the debug mode, it seems to be stopping at this:

Please Login or Register  to view this content.

**vbatech** · 03-07-2012, 12:07 PM

Attached are the files, hopefully this helps. Thanks,

Kevin

**OnErrorGoto0** · 03-07-2012, 12:39 PM

Please Login or Register  to view this content.

**vbatech** · 03-07-2012, 02:53 PM

Thanks Rory.

Another question that I have is if I have text formatting such as bullet points, bold, etc. is there a way to save that formatting so that in the destination_file it has the same formatting?

Also, in the actual text box it seems like formatting capabilities are not avaialble. For example, I can't bullet point the text without bulleting everything. Is there a way to fix this?

Thanks,

Kevin

**OnErrorGoto0** · 03-07-2012, 04:46 PM

It's probably easier to just copy the whole text box to the other workbook?

**vbatech** · 03-07-2012, 04:58 PM

I think that might work. How would we be able to go about doing that?

**vbatech** · 03-08-2012, 05:30 PM

Hi Rory,

Sorry for the change but I think it is best if we could copy and paste the text from one text box to another, with the formatting from the source file preserved when pasting to the destination file. Thanks,

Kevin

**OnErrorGoto0** · 03-08-2012, 07:28 PM

May I ask why? I think that will be a lot of code.

**vbatech** · 03-08-2012, 07:39 PM

Hi Rory,

A few of the reasons being:
- there are going to be multiple textboxes with specify names assigned to it in the destination file
- location of the textbox is key in order to make my final file consistent each time that I run this macro

The best way I believe is to do a copy and paste with formatting. Thanks,

Kevin

**OnErrorGoto0** · 03-09-2012, 04:45 AM

I'm afraid I think that will require more work than I have time for, unless I come across a simple method. Hopefully someone else will join in.

**vbatech** · 03-13-2012, 10:51 AM

Does anyone have any thoughts on how this would be possible?

**vbatech** · 03-14-2012, 11:14 AM

Hi Rory, I haven't heard back from anyone else. I was hoping you might have a solution. Thanks,

Kevin

**Firefly2012** · 03-14-2012, 05:37 PM

I've never used them, but would using RichTextBoxes instead be an option? I presume it's easy to copy the formatting of the text from them (may be wrong here(. Alternatively with InkEdit controls you definitely can copy the Rich Text Formatted content so this again would be a relatively simple option (with the caveat you then have these less common ActiveX controls on your worksheet with the possible consequences they might bring).

Compile Error: Method of data member not found (VBA)

LinkBack

Thread Tools

Rate This Thread

Display

Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Re: Compile Error: Method of data member not found (VBA)

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions