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]

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

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

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.

 

[mikerickson]

There is no Ranges property of a worksheet. Try

ws.Cells(irow, 3).Value = Sheet1.Range("K8").Value

 

[Hasanain Shuja]

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)

 

[mikerickson]

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.