Can a form show a cell value

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
Although this project would be done better in ACCESS, I'm stuck doing it in Excel. Thanks for bearing with me.
I have created a form where I select a machine and part number from a drop down menu. Once selected, these values are placed into an Excel spreadsheet and I run an INDEX/MATCH function to pull data from a larger sheet. At this point, all this data resides within a single row on the targeted spreadsheet.

I want to show this data on a form that I created in excel. The idea is to print the form and use the parameters to setup the part number job on the machine again.

I have the following sample code related to trying to return the cell value to the form:

Private Sub button_enter_click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("retrieve")
irow = ws.Cells(Rows.Count, 9)

'enter Machine and Part Number parameters into Retrieve sheet
ws.Range("b6").Value = txt_machine.Value
ws.Range("d6").Value = TXT_PARTNUMBER.Value
Exit Sub

'return data FROM sheet to FORM
txt_lastdate.Text = ws.cells(irow,9).Value
End Sub

I have tried txt_lastdate.value = ws.cells(irow,9).Value.
I have tried txt_lastdate.value = ws.range("a9").Value.

The actual data in cell A9 is the return value of the INDEX/MATCH function. Does this make a difference? If so, how do I get around it?

I get error stating OUT OF MEMORY. I have no other application running, and have only a total of 3 routines totaling 30 lines. This is not a memory error that I can ascertain.

Thanks in advance for all help provided. I'm stumped!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
You appear to be exiting the procedure before you get to txt_lastdate.Text = ws.cells(irow,9).Value. Is that how you see the code?

irow = ws.Cells(Rows.Count, 9) doesn't look right. You might want to check the value in irow. The last used row in column I is ws.Cells(Rows.Count, 9).End(xlUp).Row.

Do you have any event handlers running - maybe Worksheet_Change?
 
Last edited:

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
Thanks Ruddles! The issue was that I was ending the routine before I was getting to Txt_lastdate. The irow part of the code did not work for me so I just used range. I figure the irow thing out later. It's always the simple stuff that messes with one's code! Thanks again.
 

Forum statistics

Threads
1,141,842
Messages
5,708,916
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top