Various textboxes empty on userform

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,232
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I have a worksheet database of which if i double click a customers name in column A a userform opens & all his data "of which is the same as worksheet" is shown.
This works fin BUT TextBoxes 2-7 are empty ?
The worksheet row is A-W

Do you see an error as to why this is happening.
Thanks

This is the double click code.
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
End Sub

This is the Database Load Data code
Code: 
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Set m_ws = ws
    m_rw = rw
    Me.txtCustomer.Value = m_ws.Range("A" & m_rw).Value
    Me.txtRegistrationNumber.Value = m_ws.Range("B" & m_rw).Value
    Me.txtBlankUsed.Value = m_ws.Range("C" & m_rw).Value
    Me.txtVehicle.Value = m_ws.Range("D" & m_rw).Value
    Me.txtButtons.Value = m_ws.Range("E" & m_rw).Value
    Me.txtKeySupplied.Value = m_ws.Range("F" & m_rw).Value
    Me.txtTransponderChip.Value = m_ws.Range("G" & m_rw).Value
    Me.txtJobAction.Value = m_ws.Range("H" & m_rw).Value
    Me.txtProgrammerCloner.Value = m_ws.Range("I" & m_rw).Value
    Me.txtKeyCode.Value = m_ws.Range("J" & m_rw).Value
    Me.txtBiting.Value = m_ws.Range("K" & m_rw).Value
    Me.txtChassisNumber.Value = m_ws.Range("L" & m_rw).Value
    Me.txtVehicleYear.Value = m_ws.Range("N" & m_rw).Value
    Me.txtPaid.Value = m_ws.Range("O" & m_rw).Value
    Me.txtInvoiceNumber.Value = m_ws.Range("P" & m_rw).Value
    Me.TextBox1.Value = m_ws.Range("Q" & m_rw).Value
    Me.TextBox2.Value = m_ws.Range("R" & m_rw).Value
    Me.TextBox3.Value = m_ws.Range("S" & m_rw).Value
    Me.TextBox4.Value = m_ws.Range("T" & m_rw).Value
    Me.TextBox5.Value = m_ws.Range("U" & m_rw).Value
    Me.TextBox6.Value = m_ws.Range("V" & m_rw).Value
    Me.TextBox7.Value = m_ws.Range("W" & m_rw).Value
    Me.ComboBoxCustomersNames.Value = m_ws.Range("A" & m_rw).Value
    
    With Me.txtCustomer
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    End With
        
    Me.Show
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
You should directly use the variable rw:

VBA Code: 
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.txtCustomer.Value = m_ws.Range("A" & rw).Value
    Me.txtRegistrationNumber.Value = m_ws.Range("B" & rw).Value
    Me.txtBlankUsed.Value = m_ws.Range("C" & rw).Value
'...and so on
 
Upvote 0
Hi,
The code you advise, i use for the double click code OR the change what the LoadData currently is ?

Sometimes im unable to decifer the code replies sorry.
 
Upvote 0
Replace your codes with the following:

VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then Exit Sub
    Cancel = True
    Database.LoadData Me, Target.Row
End Sub

VBA Code: 
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.txtCustomer.Value = m_ws.Range("A" & rw).Value
    Me.txtRegistrationNumber.Value = m_ws.Range("B" & rw).Value
    Me.txtBlankUsed.Value = m_ws.Range("C" & rw).Value
    Me.txtVehicle.Value = m_ws.Range("D" & rw).Value
    Me.txtButtons.Value = m_ws.Range("E" & rw).Value
    Me.txtKeySupplied.Value = m_ws.Range("F" & rw).Value
    Me.txtTransponderChip.Value = m_ws.Range("G" & rw).Value
    Me.txtJobAction.Value = m_ws.Range("H" & rw).Value
    Me.txtProgrammerCloner.Value = m_ws.Range("I" & rw).Value
    Me.txtKeyCode.Value = m_ws.Range("J" & rw).Value
    Me.txtBiting.Value = m_ws.Range("K" & rw).Value
    Me.txtChassisNumber.Value = m_ws.Range("L" & rw).Value
    Me.txtVehicleYear.Value = m_ws.Range("N" & rw).Value
    Me.txtPaid.Value = m_ws.Range("O" & rw).Value
    Me.txtInvoiceNumber.Value = m_ws.Range("P" & rw).Value
    Me.TextBox1.Value = m_ws.Range("Q" & rw).Value
    Me.TextBox2.Value = m_ws.Range("R" & rw).Value
    Me.TextBox3.Value = m_ws.Range("S" & rw).Value
    Me.TextBox4.Value = m_ws.Range("T" & rw).Value
    Me.TextBox5.Value = m_ws.Range("U" & rw).Value
    Me.TextBox6.Value = m_ws.Range("V" & rw).Value
    Me.TextBox7.Value = m_ws.Range("W" & rw).Value
    Me.ComboBoxCustomersNames.Value = m_ws.Range("A" & rw).Value
    
    With Me.txtCustomer
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    End With
        
    Me.Show
End Sub
 
Upvote 0
Hi,
Im getting confused.

The Double click code you show is the same as what i put in post #1

I then have replaced my existing Sub LoadDat code as shown in post #1 with what you advised above.

When i now double click a customers name i get a run time error.

RTE 91
Object variable or with block variable not set
 
Upvote 0
ipbr21054 said:
When i now double click a customers name i get a run time error.

RTE 91
Object variable or with block variable not set
Click to expand...

you get the error because you are not using the parameter name in your code

Rich (BB code): 
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.txtCustomer.Value = m_ws.Range("A" & rw).Value

it should be

VBA Code: 
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.txtCustomer.Value = ws.Range("A" & rw).Value

Dave
 
Upvote 0
You have this line:
Rich (BB code): 
Me.txtCustomer.Value = m_ws.Range("A" & m_rw).Value

You should use it like this:
Rich (BB code): 
Me.txtCustomer.Value = ws.Range("A" & rw).Value

You must change it on all your lines.
 
Upvote 0
ipbr21054 said:
Im getting confused.
Click to expand...

I'm sorry about that.

ipbr21054 said:
The Double click code you show is the same as what i put in post #1
Click to expand...
That code does not change.

here all the updated code.
VBA Code: 
Sub LoadData(ByVal ws As Worksheet, ByVal rw As Long)
    Me.txtCustomer.Value = ws.Range("A" & rw).Value
    Me.txtRegistrationNumber.Value = ws.Range("B" & rw).Value
    Me.txtBlankUsed.Value = ws.Range("C" & rw).Value
    Me.txtVehicle.Value = ws.Range("D" & rw).Value
    Me.txtButtons.Value = ws.Range("E" & rw).Value
    Me.txtKeySupplied.Value = ws.Range("F" & rw).Value
    Me.txtTransponderChip.Value = ws.Range("G" & rw).Value
    Me.txtJobAction.Value = ws.Range("H" & rw).Value
    Me.txtProgrammerCloner.Value = ws.Range("I" & rw).Value
    Me.txtKeyCode.Value = ws.Range("J" & rw).Value
    Me.txtBiting.Value = ws.Range("K" & rw).Value
    Me.txtChassisNumber.Value = ws.Range("L" & rw).Value
    Me.txtVehicleYear.Value = ws.Range("N" & rw).Value
    Me.txtPaid.Value = ws.Range("O" & rw).Value
    Me.txtInvoiceNumber.Value = ws.Range("P" & rw).Value
    Me.TextBox1.Value = ws.Range("Q" & rw).Value
    Me.TextBox2.Value = ws.Range("R" & rw).Value
    Me.TextBox3.Value = ws.Range("S" & rw).Value
    Me.TextBox4.Value = ws.Range("T" & rw).Value
    Me.TextBox5.Value = ws.Range("U" & rw).Value
    Me.TextBox6.Value = ws.Range("V" & rw).Value
    Me.TextBox7.Value = ws.Range("W" & rw).Value
    Me.ComboBoxCustomersNames.Value = ws.Range("A" & rw).Value
    
    With Me.txtCustomer
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    End With
        
    Me.Show
End Sub
 
Upvote 0
I have see screenshot
 

Attachments

  • 7017.jpg
    7017.jpg
    226.1 KB · Views: 8
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,473
Messages
6,125,018
Members
449,203
Latest member
tungnmqn90

Share this page

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
Back
Top