Need a update code in userform VBA

S

sandhya16

New Member
Joined
Jun 8, 2015
Messages
34
I have the below code to enter the data in the range A2:B6 using Userform.

I want to make changes to it,I want to check if combobox value already exists then i just update the corresponding textbox otherwise add a new row.Comboxbox has values-Class 0 to Class 4 and Textbox has numeric values.


Private Sub btnOk_Click()
Dim FoundCell As Range
Dim Search As String
Dim ws As Worksheet
Dim newRow As Long
Application.ScreenUpdating = False
Set ws = Worksheets("Forecast")
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A"))
Search = cbClassLevel.Text
Set FoundCell = Worksheets("Forecast").Columns(1).Find(Search, LookIn:=xlValues, Lookat:=xlWhole)
If FoundCell Is Nothing Then
ws.Cells(newRow, 1).value = Me.cbClassLevel.value
ws.Cells(newRow, 2).value = Me.TextBox1.value


Else:
MsgBox "Class already exists!" & "data found at cell address " & FoundCell.Address
End If
Application.ScreenUpdating = True
cbClassLevel.Text = ""
TextBox1.Text = ""
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi welcome to the board.

See if this code does what you want.

Place all code in your forms code page:

Code: 
Dim ws As Worksheet
Dim RecordRow As Long
Dim Action As String


Private Sub btnOk_Click()
    Dim msg As String
    
    'add new or update existing record
    ws.Cells(RecordRow, 1).Value = Me.cbClassLevel.Value
    ws.Cells(RecordRow, 2).Value = Me.TextBox1.Value
    
    Select Case Action
    Case "New"
        'update combobox list
        Me.cbClassLevel.List = ws.Range("A2:A" & RecordRow).Value
        msg = "New Record Entered"
        
    Case "Update"
        msg = "Record Updated"
        
    End Select
    
    'report result
    MsgBox Me.cbClassLevel.Text & Chr(10) & msg, 48, msg
    
    Me.cbClassLevel.Text = ""
    Me.TextBox1.Text = ""
End Sub


Private Sub cbClassLevel_Change()


    If Me.cbClassLevel = -1 Then Exit Sub
    'find record row in range - if recod does not exist
    'next blank row in range is returned
    RecordRow = GetRow(Target:=ws.Columns(1), Search:=Me.cbClassLevel.Text)
    'return value to textbox
    Me.TextBox1.Value = ws.Cells(RecordRow, 2).Value
    
End Sub


Private Sub UserForm_Initialize()


    'intialize ws object variable
    Set ws = Worksheets("Forecast")
    
    'populate combobox list
    With Me.cbClassLevel
    .RowSource = ""
    .List = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).Value
    'show first record
    .ListIndex = 0
    End With
End Sub


Function GetRow(ByVal Target As Range, ByVal Search As String) As Long
    Dim FoundCell As Range
    'find the next blank row in range
    GetRow = Target.Parent.Cells(Target.Parent.Rows.Count, Target.Column).End(xlUp).Row + 1
    Action = "New"
    'check if record exists
    Set FoundCell = Target.Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    'if exists - return record row
    If Not FoundCell Is Nothing Then GetRow = FoundCell.Row: Action = "Update"
End Function

Note the variables that sit outside any procedure. these must be placed at very top of your forms code page.

Code is not fully tested but when form opened, it should load all values in your target range (Column 1) in your combobox & populate the textbox with correct record. This should happen either if you select a record from list or type it directly into the combobox. All existing records should Update when you press the command button.

Where a record does not exist, it will be added to the next available row in range and your combobox list updated.

You should adjust code as required to meet your project need.

Hope Helpful

Dave
 
Upvote 0
thanks Dave for your reply.I am still facing problem in it.i added the following code to my userform.I want to populate the combobox value when the userform initializes but when i am doing that it is not populating "Class 0 to Class 4" values.
Also i put the function get row and cbClassLevelChange in the module.What changes should i do?


Dim id As Integer, i As Integer, j As Integer, flag As Boolean


Dim ws As Worksheet
Dim RecordRow As Long
Dim Action As String


Private Sub btnOk_Click()
Dim msg As String

'add new or update existing record
ws.Cells(RecordRow, 1).value = Me.cbClassLevel.value
ws.Cells(RecordRow, 2).value = Me.TextBox1.value

Select Case Action
Case "New"
'update combobox list
Me.cbClassLevel.List = ws.Range("A2:A" & RecordRow).value
msg = "New Record Entered"

Case "Update"
msg = "Record Updated"

End Select

'report result
' MsgBox Me.cbClassLevel.Text & Chr(10) & msg, 48, msg

Me.cbClassLevel.Text = ""
Me.TextBox1.Text = ""
End Sub


Private Sub CommandButton1_Click()
cbClassLevel.Text = ""
TextBox1.Text = ""
End Sub


Private Sub btnCancel_Click()
Unload Me
End Sub


Private Sub TextBox1_Change()
OnlyNumbers


End Sub


Private Sub OnlyNumbers()
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.value) And .value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.value = vbNullString
End If
End With
End If
End Sub


Private Sub UserForm_Initialize()


'intialize ws object variable
Set ws = Worksheets("Forecast")

'populate combobox list
With Me.cbClassLevel
' .AddItem "Class 0"
'.AddItem "Class 1"
'.AddItem "Class 2"
'.AddItem "Class 3"
'.AddItem "Class 4"
.RowSource = ""
.List = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).value
'show first record
.ListIndex = 0
End With


End Sub















dmt32 said:
Hi welcome to the board.

See if this code does what you want.

Place all code in your forms code page:

Code: 
Dim ws As Worksheet
Dim RecordRow As Long
Dim Action As String


Private Sub btnOk_Click()
    Dim msg As String
    
    'add new or update existing record
    ws.Cells(RecordRow, 1).Value = Me.cbClassLevel.Value
    ws.Cells(RecordRow, 2).Value = Me.TextBox1.Value
    
    Select Case Action
    Case "New"
        'update combobox list
        Me.cbClassLevel.List = ws.Range("A2:A" & RecordRow).Value
        msg = "New Record Entered"
        
    Case "Update"
        msg = "Record Updated"
        
    End Select
    
    'report result
    MsgBox Me.cbClassLevel.Text & Chr(10) & msg, 48, msg
    
    Me.cbClassLevel.Text = ""
    Me.TextBox1.Text = ""
End Sub


Private Sub cbClassLevel_Change()


    If Me.cbClassLevel = -1 Then Exit Sub
    'find record row in range - if recod does not exist
    'next blank row in range is returned
    RecordRow = GetRow(Target:=ws.Columns(1), Search:=Me.cbClassLevel.Text)
    'return value to textbox
    Me.TextBox1.Value = ws.Cells(RecordRow, 2).Value
    
End Sub


Private Sub UserForm_Initialize()


    'intialize ws object variable
    Set ws = Worksheets("Forecast")
    
    'populate combobox list
    With Me.cbClassLevel
    .RowSource = ""
    .List = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).Value
    'show first record
    .ListIndex = 0
    End With
End Sub


Function GetRow(ByVal Target As Range, ByVal Search As String) As Long
    Dim FoundCell As Range
    'find the next blank row in range
    GetRow = Target.Parent.Cells(Target.Parent.Rows.Count, Target.Column).End(xlUp).Row + 1
    Action = "New"
    'check if record exists
    Set FoundCell = Target.Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    'if exists - return record row
    If Not FoundCell Is Nothing Then GetRow = FoundCell.Row: Action = "Update"
End Function

Note the variables that sit outside any procedure. these must be placed at very top of your forms code page.

Code is not fully tested but when form opened, it should load all values in your target range (Column 1) in your combobox & populate the textbox with correct record. This should happen either if you select a record from list or type it directly into the combobox. All existing records should Update when you press the command button.

Where a record does not exist, it will be added to the next available row in range and your combobox list updated.

You should adjust code as required to meet your project need.

Hope Helpful

Dave
Click to expand...
 
Upvote 0
Thanks a lot dave.Your code worked although i have one more question-

Is there any where to protect the column A2:A6 values from delete by a user?A2:A6 has the values that are being populated in the combobox and B2:B6 has textbox values.
 
Upvote 0
Hi,
you can apply protection to the worksheet.

Place this code in the Thisworkbook Module

Code: 
Private Sub Workbook_Open()


    Sheets("Forecast").Protect Password:="", _
    UserInterFaceOnly:=True


End Sub

Save & Close workbook.

Each time your workbook is opened, your worksheet should be fully protected & updates / changes to it can only be made via your userform.

Add password as required.

Dave
 
Upvote 0
Thanks a lot dave.


dmt32 said:
Hi,
you can apply protection to the worksheet.

Place this code in the Thisworkbook Module

Code: 
Private Sub Workbook_Open()


    Sheets("Forecast").Protect Password:="", _
    UserInterFaceOnly:=True


End Sub

Save & Close workbook.

Each time your workbook is opened, your worksheet should be fully protected & updates / changes to it can only be made via your userform.

Add password as required.

Dave
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help Needed
2
Replies
13
Views
319
kwavic
K
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
A
  • Question Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
484
amoverton2
A
N
  • Question Question
Excel VBA - issue with my VBA code
Replies
3
Views
340
dmt32
D
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
72
RoryA
RoryA

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

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