PDA

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



rrosa1
09-09-2010, 05:44 PM
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
09-09-2010, 10:56 PM
Where have you declared the variable sh?

rrosa1
09-09-2010, 11:51 PM
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
09-10-2010, 12:10 AM
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
09-10-2010, 12:25 AM
oh
sh means sheet of workbook as in the code "Today"

Simon Lloyd
09-10-2010, 12:27 AM
When uploading a workbook please make sure that ALL protection is removed from the worksheets and the VBA project.

rrosa1
09-10-2010, 12:37 AM
i did not know sorry for trouble there is no pw protection u can unprotecte without pw

Simon Lloyd
09-10-2010, 02:21 AM
Use this code:
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

rrosa1
09-10-2010, 03:26 AM
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
:dunno


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
09-10-2010, 04:21 AM
For some reason, ComboBox1_change is being fired at line
.Cells(n, 1).Value = Me.trmn.Text 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
Public bEdit as Boolean
Then just before the first mentioned line above add
bEdit = True
Then in ComboBox1_Change comment out or delete the line
Dim bEdit As Boolean
Hope this helps!

Simon Lloyd
09-10-2010, 06:09 AM
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
09-10-2010, 05:12 PM
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
09-10-2010, 05:34 PM
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
:clap::bow:

Tinbendr
09-10-2010, 07:09 PM
Glad I could help.