Textbox trouble on a user form

[Oreg]

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??

 

[Bob Phillips]

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)

 

[Bernie Deitrick]

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]

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

 

[Oreg]

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

 

[Bernie Deitrick]

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

 

[Bob Phillips]

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]

Thanks,

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