Excel VBA Display text box vs. Input text box values fail to transfer with display text box method, Compile error: Method or data member not found

Hasanain Shuja

New Member
Joined
Jun 25, 2020
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
Hi, I have 2 scenarios where:
1. I have an integrated VBA form where values are input manually in a textbox and I get results outputted successfully.
I was able to find an i.e. =embed((m1r1) command in the textbox but can't seem to access it again.

The first sheet has code like this where it transfers it to a row below in the same spreadsheet after pressing add and then the values are cleared in the textbox

VBA Code:
ws.Cells(irow, 2).Value = Me.m1r1.Value
ws.Cells(irow, 3).Value = Me.m1r2.Value
ws.Cells(irow, 4).Value = Me.m1r3.Value
ws.Cells(irow, 5).Value = Me.m1r4.Value
ws.Cells(irow, 6).Value = Me.m1r5.Value

2. I have a 2nd modified file with an integrated VBA form where the values are derived from another spreadsheet inside the same workbook.
I made references to the first spreadsheet with the syntax below. The textbox is equal to a cell value from the other sheet so can't input text into it.
However, when I click the add button it gives me Compile error: Method or data member not found

VBA Code:
ws.Ranges(irow, 3).Value = Sheet1.Range("K8").Value

ws.Ranges(irow, 4).Value = Sheet1.Range("L8").Value

ws.Ranges(irow, 5).Value = Sheet1.Range("J9").Value

ws.Ranges(irow, 6).Value = Sheet1.Range("K9").Value

I'm using the property value (Name) to reference the spreadsheet. I'd prefer to just use the values straight from the textbox but can't find a way to click the textbox and figure out it's name.
There doesn't seem to be a form in VBA when I open it up so the form seems integrated into the spreadsheet.

Ideally I want to be able to just take the value from the display spreadsheet cell and have it added in the rows below.
i.e. me.m1r1textbox.Value but I can't find a properties tab for those boxes after I added a formula into them.

Any ideas what I may be doing wrong? Thanks.

display vs input sheets.PNG
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,838
There is no Ranges property of a worksheet. Try
VBA Code:
ws.Cells(irow, 3).Value = Sheet1.Range("K8").Value
 

Hasanain Shuja

New Member
Joined
Jun 25, 2020
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
Hi I did try it and it still gets the same error:

Compile error: Method or data member not found

The original textbox cell allowed for manually inputting a value but it also said:
I couldn't find a way to access that formula again in the older spreadsheet that has the textboxes for inputting values.
=EMBED("m1r1".....can't remember all of the syntax)

data added to row below.PNG
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,838
I would suspect that the problem lies with the EMBED command. AFAIK, its not a standard Excel feature. And none of the code you posted uses it, so we can't comment on your use of it.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,225
Members
412,372
Latest member
JON_ROCKS
Top