ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problem with a Userform (https://www.excelbanter.com/excel-programming/429485-problem-userform.html)

excelent
Problem with a Userform
 
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
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

Bernie Deitrick
Problem with a Userform
 
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
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

"excelent" wrote in message
...
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
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

excelent
Problem with a Userform
 
Bernie... wery nice job tks. :-)

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

"Bernie Deitrick" skrev:

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

"excelent" wrote in message
...
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
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


All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com