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
2007
Platform
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,651
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
2007
Platform
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,651
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,102,907
Messages
5,489,654
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top