Solved: how can i use the userfrom to replace the value in sh

[rrosa1]

hi
i am not pro in VBA and i have no idea what is the problem with this code since some one help to construct this code and it was working fine as code suppose to get the data from today sh in userform
and if user change the value in user from and press edit button it save the new value with old in today sh.but now it do not replace the new value with old value .
so if some one can help it highly appreciated
thanks

Private Sub cmdOkay_Click()
    Dim n As Long
    Dim WS As Worksheet

    Set WS = ThisWorkbook.Worksheets("Today")
    'UnProtect the sheet before adding the data
    WS.Unprotect Password:=""

    If Trim(Me.ComboBox1.Value) = "" Then
        Me.ComboBox1.SetFocus
        MsgBox "Please Select the No"
        Exit Sub
    End If

    With WS
        'n = Me.ComboBox1.ListIndex + 8
       n = Application.Match(CLng(Me.ComboBox1.Value), .Range("A:a"), 0)
 
        .Cells(n, 1).Value = Me.trmn.Text
        .Cells(n, 2).Value = Me.days.Text
        .Cells(n, 3).Value = UCase(Me.txtFName.Text)
        .Cells(n, 5).Value = Me.txtloan.Text
        .Cells(n, 6).Value = Me.ptype.Text
        .Cells(n, 14).Value = UCase(Me.txtLName.Text)
        .Cells(n, 15).Value = Now
        .Cells(n, 16).Value = Me.txtopen.Text
        
        'clear the data
        With Me
            .ptype.Value = ""
            .trmn.Value = ""
            .txtloan.Value = ""
            .txtopen.Value = ""
            .days.Value = ""
            .txtLName.Value = ""
            .txtFName.Value = ""
        End With

        'Protect the sheet after adding the data
        'Sheets("Today").Protect Password:=""
        .Protect Password:=""
        .EnableSelection = xlUnlockedCells
    End With

    Unload Me

    ActiveWorkbook.Save
End Sub

[Simon Lloyd]

Where have you declared the variable sh?

[rrosa1]

hi Simon
there is the code it populate the combobox
code as

Private Sub ComboBox1_Change()
    Dim vreg As String
    Dim drow As Integer
    Dim c As Range
    Dim bEdit As Boolean

    If bEdit Then Exit Sub
    vreg = ComboBox1.Value
    With Sheets("Today").Range("rmnos")
        Set c = .Find(vreg, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then drow = c.Row
    End With
    With Me
        .txtFName.Value = Sheets("Today").Cells(drow, 3).Value
        .trmn.Value = Sheets("Today").Cells(drow, 1).Value
        .txtLName.Value = Sheets("Today").Cells(drow, 14).Value
        .ptype.Value = Sheets("Today").Cells(drow, 6).Value
        .days.Value = Sheets("Today").Cells(drow, 2).Value
        .txtloan.Value = Sheets("Today").Cells(drow, 5).Value
        .txtopen.Value = Sheets("Today").Cells(drow, 16).Value
    End With
End Sub

this is what u asking?

[Simon Lloyd]

You mention this "how can i use the userfrom to replace the value in sh " you don't have a variable sh which is why i asked you where it's declared.

[rrosa1]

oh
sh means sheet of workbook as in the code "Today"

[Simon Lloyd]

When uploading a workbook please make sure that ALL protection is removed from the worksheets and the VBA project.

[rrosa1]

i did not know sorry for trouble there is no pw protection u can unprotecte without pw

[Simon Lloyd]

Use this code:
[vba]Private Sub cmdOkay_Click()
Dim n As Long
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Today")
'UnProtect the sheet before adding the data
WS.Unprotect Password:=""
If Trim(Me.ComboBox1.Value) = "" Then
Me.ComboBox1.SetFocus
MsgBox "Please Select the No"
Exit Sub
End If

With WS
'n = Me.ComboBox1.ListIndex + 8
n = Application.Match(CLng(Me.ComboBox1.Value), .Range("A:a"), 0)
If Me.trmn.Value <> Me.ComboBox1.Value Then
.Cells(n, 1).Value = Me.trmn.Text
GoTo Nxt:
End If
.Cells(n, 1).Value = Me.trmn.Text
.Cells(n, 2).Value = Me.days.Text
.Cells(n, 3).Value = UCase(Me.txtFName.Text)
.Cells(n, 5).Value = Me.txtloan.Text
.Cells(n, 6).Value = Me.ptype.Text
.Cells(n, 14).Value = UCase(Me.txtLName.Text)
.Cells(n, 15).Value = Now
.Cells(n, 16).Value = Me.txtopen.Text
Nxt:
'clear the data
With Me
.ptype.Value = ""
.trmn.Value = ""
.txtloan.Value = ""
.txtopen.Value = ""
.days.Value = ""
.txtLName.Value = ""
.txtFName.Value = ""
End With
'Protect the sheet after adding the data
'Sheets("Today").Protect Password:=""
.Protect Password:=""
.EnableSelection = xlUnlockedCells
End With
Unload Me
ActiveWorkbook.Save
End Sub[/vba]

[rrosa1]

hi Simon
thanks for the code
but still it not doing as i need to do.
as suppose i need to change the no ,name,payment .
code change only the no not the name or payment etc.
sorry to be pain in a** but i can't get it work with wb

If Me.trmn.Value <> Me.ComboBox1.Value Then
            .Cells(n, 1).Value = Me.trmn.Text
            GoTo Nxt:
End If

if i may understand this code
as check the value in trmn as not as combobox1 than it change the value
of cell(n,1) to trmn.
but actually if user modify any taxbox in user from it should change the value of corresponding cell in same row of combobox1 row.
i hope i describe my problem.

[Tinbendr]

For some reason, ComboBox1_change is being fired at line
[vba].Cells(n, 1).Value = Me.trmn.Text[/vba] This resets all the values of the userform. So the value you just entered into the userform is rewritten with sheet data. Strange....

The workaround is to Declare bEdit as a Public variable. I noticed the If/Then for this in the ComboBox1_Change already. The original coder must have had the same problem. So, goto the very top of the code box in the userform panel and under Option Explicit add
[vba]Public bEdit as Boolean[/vba]
Then just before the first mentioned line above add
[vba]bEdit = True[/vba]
Then in ComboBox1_Change comment out or delete the line
[VBA]Dim bEdit As Boolean[/VBA]
Hope this helps!

[Simon Lloyd]

In your example you showed that you wanted the numebr e.g 104 to change if it was chaged in the combobox but nothing else to change, thats what is happening!

Also as David pointed out your Combobox1_Change event will also cause you some problems.

I can't grasp what you to happen and when under what conditions.

[rrosa1]

hi
David
as u mention i change the code the way u suggested but still no luck for me
hear is the new code as per your suggestion

Option Explicit
Public bEdit As Boolean
Private Sub cmdCancel_Click()
    Unload Me
End Sub
Private Sub cmdOkay_Click()
    Dim n As Long
    Dim WS As Worksheet
    Set WS = ThisWorkbook.Worksheets("Today")

    WS.Unprotect Password:=""
    If Trim(Me.ComboBox1.Value) = "" Then
        Me.ComboBox1.SetFocus
        MsgBox "Please Select the No"
        Exit Sub
    End If

    With WS
        n = Application.Match(CLng(Me.ComboBox1.Value), .Range("A:A"), 0)
        .Cells(n, 1).Value = Me.trmn.Text
        bEdit = True
        .Cells(n, 2).Value = Me.days.Text
        .Cells(n, 3).Value = UCase(Me.txtFName.Text)
        .Cells(n, 5).Value = Me.txtloan.Text
        .Cells(n, 6).Value = Me.ptype.Text
        .Cells(n, 14).Value = UCase(Me.txtLName.Text)
        .Cells(n, 15).Value = Now
        .Cells(n, 16).Value = Me.txtopen.Text

        'clear the data
        With Me
            .ptype.Value = ""
            .trmn.Value = ""
            .txtloan.Value = ""
            .txtopen.Value = ""
            .days.Value = ""
            .txtLName.Value = ""
            .txtFName.Value = ""
        End With
        'Protect the sheet after adding the data
        .Protect Password:=""
        .EnableSelection = xlUnlockedCells
    End With
    Unload Me
    ActiveWorkbook.Save
End Sub
Private Sub ComboBox1_Change()
    Dim vreg As String
    Dim drow As Integer
    Dim c As Range
    'Dim bEdit As Boolean

    If bEdit Then Exit Sub
    vreg = ComboBox1.Value
    With Sheets("Today").Range("rmnos")
        Set c = .Find(vreg, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then drow = c.Row
    End With
    With Me
        .txtFName.Value = Sheets("Today").Cells(drow, 3).Value
        .trmn.Value = Sheets("Today").Cells(drow, 1).Value
        .txtLName.Value = Sheets("Today").Cells(drow, 14).Value
        .ptype.Value = Sheets("Today").Cells(drow, 6).Value
        .days.Value = Sheets("Today").Cells(drow, 2).Value
        .txtloan.Value = Sheets("Today").Cells(drow, 5).Value
        .txtopen.Value = Sheets("Today").Cells(drow, 16).Value
    End With
End Sub
Private Sub UserForm_Initialize()
    Me.ComboBox1.RowSource = "rmnos"
    Me.ptype.RowSource = "paytype"

End Sub

thanks for your help and sorry for late responce.

hi Simon
no user need to modify all data of today sheet by userform in column
A,B,C,E.F,N and P in same row by this form
since i have anther userform to input the data and that work.but
modifying data userform not work
thanks for all your help .

[rrosa1]

hi David and Simon
i change this code
from this

With WS
        n = Application.Match(CLng(Me.ComboBox1.Value), .Range("A:A"), 0)
       
        .Cells(n, 1).Value = Me.trmn.Text
 bEdit = True        
        .Cells(n, 2).Value = Me.days.Text
        .Cells(n, 3).Value = UCase(Me.txtFName.Text)
        .Cells(n, 5).Value = Me.txtloan.Text
        .Cells(n, 6).Value = Me.ptype.Text
        .Cells(n, 14).Value = UCase(Me.txtLName.Text)
        .Cells(n, 15).Value = Now
        .Cells(n, 16).Value = Me.txtopen.Text

        'clear the data

to this

With WS
        n = Application.Match(CLng(Me.ComboBox1.Value), .Range("A:A"), 0)
     bEdit = True
        .Cells(n, 1).Value = Me.trmn.Text
        
        .Cells(n, 2).Value = Me.days.Text
        .Cells(n, 3).Value = UCase(Me.txtFName.Text)
        .Cells(n, 5).Value = Me.txtloan.Text
        .Cells(n, 6).Value = Me.ptype.Text
        .Cells(n, 14).Value = UCase(Me.txtLName.Text)
        .Cells(n, 15).Value = Now
        .Cells(n, 16).Value = Me.txtopen.Text

        'clear the data

and hallelujah it work the way intended

thanks for your help guys.
it really appreciated

[Tinbendr]

Glad I could help.