Hello,

I've created a user form with various text boxes and comboboxes whic
enters info into specific columns on a worksheet named "METRO". Th
code I used to enter the data searches for the last cell, (in METRO)
with data in a specific column and offsets it by one so the new info
(plugged into the form), is entered in the next "empty" row. I'v
listed a portion of the code I used below.
r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row

Column A of worksheet METRO is labeled as 'ticket number" which i
populated by a formula that is linked to another sheet.


On the user form I've created is a textbox I would like to have displa
the ticket number that the data entered into the form will populate.
I hope I didn't get too long winded or confusing. Any Ideas??

--
Message posted from http://www.ExcelForum.com

Textbox1.Text = ws.Cells(r,"A").Text?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oreg " wrote in message
...
Hello,

I've created a user form with various text boxes and comboboxes which
enters info into specific columns on a worksheet named "METRO". The
code I used to enter the data searches for the last cell, (in METRO),
with data in a specific column and offsets it by one so the new info,
(plugged into the form), is entered in the next "empty" row. I've
listed a portion of the code I used below.
r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row

Column A of worksheet METRO is labeled as 'ticket number" which is
populated by a formula that is linked to another sheet.


On the user form I've created is a textbox I would like to have display
the ticket number that the data entered into the form will populate.
I hope I didn't get too long winded or confusing. Any Ideas???


---
Message posted from http://www.ExcelForum.com/

Oreg,

After r is set using this:
r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row

The corresponding ticket number should be
ws.Range("A"& r).Value

You can assign it to the textbox using code like
Userform1.Textbox1.Text = ws.Range("A"& r).Value

HTH,
Bernie
MS Excel MVP

"Oreg " wrote in message
...
Hello,

I've created a user form with various text boxes and comboboxes which
enters info into specific columns on a worksheet named "METRO". The
code I used to enter the data searches for the last cell, (in METRO),
with data in a specific column and offsets it by one so the new info,
(plugged into the form), is entered in the next "empty" row. I've
listed a portion of the code I used below.
r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row

Column A of worksheet METRO is labeled as 'ticket number" which is
populated by a formula that is linked to another sheet.


On the user form I've created is a textbox I would like to have display
the ticket number that the data entered into the form will populate.
I hope I didn't get too long winded or confusing. Any Ideas???


---
Message posted from http://www.ExcelForum.com/

It took some play'n with but I finally got it. Thanks Bernie and Bob.
Never would've got it. Thanks again!!!

--
Message posted from http://www.ExcelForum.com

One last question for you. How would I get the textbox t
automatically update to the next ticket number after info is added.
Currently, with your help, it displays the same ticket number afte
data is added. Thanks guys

--
Message posted from http://www.ExcelForum.com

Oreg,

Use one of the events of one of your objects being filled in to force a
recalc of your r value, and of the textbox text.

HTH,
Bernie
MS Excel MVP

"Oreg " wrote in message
...
One last question for you. How would I get the textbox to
automatically update to the next ticket number after info is added.
Currently, with your help, it displays the same ticket number after
data is added. Thanks guys!


---
Message posted from http://www.ExcelForum.com/

Hi Oreg,

In the METRO worksheet


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 1 Then
With Target
Userform1.Textbox1.Text = .Text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oreg " wrote in message
...
One last question for you. How would I get the textbox to
automatically update to the next ticket number after info is added.
Currently, with your help, it displays the same ticket number after
data is added. Thanks guys!


---
Message posted from http://www.ExcelForum.com/

Thanks,

Once again...worked great...have you ever been stumped??

--
Message posted from http://www.ExcelForum.com