Problem with a Userform

  • Thread starter Thread starter excelent
  • Start date Start date
E

excelent

Hi experts
For som reason i cant save changes i made in a form to sheets "Ark2"
I start showing a list of names/addresses... (Private Sub liste_Click)
Then i pick a name from the list (ListBox1_Click) and then make the changes
Then i click save changes (cmdOk_Click).. but no changes is writed to the
shet !!!
Anybody have a cloue ? help please :-)

Private Sub liste_Click()
'show list of names
ListIndex = 0
rk = Sheets("Ark2").Cells(100, 1).End(xlUp).Row
ListBox1.ColumnCount = 4
ListBox1.RowSource = "Ark2!A1:D" & rk
Me.ListBox1.Visible = True
End Sub
------------------------------------------
Private Sub ListBox1_Click()
' chose a name in listbox
Dim x As Variant
rk = Sheets("Ark2").Cells(100, 1).End(xlUp).Row
x = Range("Ark2!A1:D" & rk)
valg = Me.ListBox1.ListIndex + 1
Me.txtNavn = x(valg, 1)
Me.txtAdresse = x(valg, 2)
Me.txtArbnr = x(valg, 3)
Me.txtAfd = x(valg, 4)
Me.ListBox1.Visible = False
ListIndex = 0
End Sub
--------------------------------------------
Private Sub cmdOk_Click()
'Save changes on heet
Worksheets("Ark2").Activate
rk = Cells(100, 1).End(xlUp).Row + 1
If Application.WorksheetFunction.CountIf(Range("C2:C" & rk), CDec(txtArbnr))
0 Then
Click to expand...
rk = Range("C2:C" & rk).Find(CDec(txtArbnr), LookIn:=xlValues).Row
End If
Cells(rk, 1) = Me.txtNavn
Cells(rk, 2) = Me.txtAdresse
Cells(rk, 3) = CDec(Me.txtArbnr)
Cells(rk, 4) = Me.txtAfd
Worksheets("Ark2").Range("A2:A" & rk).Name = "Navne"
End Sub
 
Try the code below - your textboxes are being overwritten when the OK button
is clicked because of the linking to the list. This just sets the list, and
ignores the linking.

You also ignored used row 1 in your liste code, but ignore it in your OK
code... not sure if that was intentional.

HTH,
Bernie
MS Excel MVP



Private Sub liste_Click()
'show list of names
ListIndex = 0
rk = Sheets("Ark2").Cells(100, 1).End(xlUp).Row
ListBox1.ColumnCount = 4
ListBox1.List = Worksheets("Ark2").Range("A1:D" & rk).Value
Me.ListBox1.Visible = True
End Sub
'------------------------------------------
Private Sub ListBox1_Click()
' chose a name in listbox
Dim x As Variant
rk = Sheets("Ark2").Cells(100, 1).End(xlUp).Row
x = Range("Ark2!A1:D" & rk)
valg = Me.ListBox1.ListIndex + 1
Me.txtNavn = x(valg, 1)
Me.txtAdresse = x(valg, 2)
Me.txtArbnr = x(valg, 3)
Me.txtAfd = x(valg, 4)
Me.ListBox1.Visible = False
ListIndex = 0
End Sub
'--------------------------------------------
Private Sub cmdOk_Click()
'Save changes on heet
Worksheets("Ark2").Activate
rk = Cells(100, 1).End(xlUp).Row + 1
If Application.WorksheetFunction.CountIf(Range("C1:C" & rk), CDec(txtArbnr))
0 Then
Click to expand...
rk = Range("C1:C" & rk).Find(CDec(txtArbnr), LookIn:=xlValues).Row
End If
Application.EnableEvents = False
Cells(rk, 1).Value = Me.txtNavn.Text
Cells(rk, 2).Value = Me.txtAdresse.Text
Cells(rk, 3).Value = CDec(Me.txtArbnr.Text)
Cells(rk, 4).Value = Me.txtAfd.Text
Worksheets("Ark2").Range("A2:A" & rk).Name = "Navne"
Application.EnableEvents = True
End Sub
 
Bernie... wery nice job tks. :-)

by the way row 1 in list is headline so thats why...

"Bernie Deitrick" skrev:
 
Back
Top