Trying to create 'Update' button in an excel form

S

skitzz

New Member
Joined
Jun 8, 2015
Messages
4
Ok, so I have created a form that i am using to enter projects that will be proposed for the upcoming year. When the user enters the project info, it gets dropped into a separate sheet ('ProjectData') where i have created a table ('Projectdatatable'). I have the following buttons on the form:

Submit - once user enters data, they click this and it adds the data to 'project datatable'
Previous - navigates though records on the form
Next - navigates through records on the form
Close form - closes form
clear form - clears contents of form (but does not delete data from the table)

I want to add an 'Update' button and to a lesser extent a 'Delete' button. For now lets just focus on the 'Update' button. I want to be able to have the user go in enter their projects and when they navigate through the form, select a previously entered project record and update the data directly in the form and click 'update record', thus updating the data record in the table 'projectdatatable' .

I have tried several methods I have researched and found online, none of which have worked. I am not a VBA expert by no means and am having trouble getting this feature to work. Any thoughts? I have pasted my entire code for my file for reference in hopes i can finally get an answer. Ive literaly searched for weeks . Thnaks in advanbce for all your help! So far i have found some good stuff on here!


Code: 
Private Sub cmbPlanEnd_Change()


End Sub


Private Sub cmbPlanStart_Change()


End Sub


Private Sub cmdAdd_Click()


Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("ProjectData")


'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


lPart = Me.Control2.ListIndex


'check for a project name
If Trim(Me.Control1.Value) = "" Then
  Me.Control1.SetFocus
  MsgBox "Please enter a Project Name"
  Exit Sub
End If


'check for a project trigger
If Trim(Me.Control2.Value) = "" Then
  Me.Control2.SetFocus
  MsgBox "Please select a Project Trigger"
  Exit Sub
End If




'check for a portfolio
If Trim(Me.Control4.Value) = "" Then
  Me.Control4.SetFocus
  MsgBox "Please select a Portfolio"
  Exit Sub
End If


'check for a Sub-Portfolio
If Trim(Me.Control5.Value) = "" Then
  Me.Control5.SetFocus
  MsgBox "Please select a Sub-Portfolio"
  Exit Sub
End If


'check for a Portfolio Objective
If Trim(Me.Control6.Value) = "" Then
  Me.Control6.SetFocus
  MsgBox "Please select a Portfolio Objective"
  Exit Sub
End If
'check for a Project Description
If Trim(Me.Control7.Value) = "" Then
  Me.Control7.SetFocus
  MsgBox "Please enter a Project Description"
  Exit Sub
End If
'check for Project Scope
If Trim(Me.control8.Value) = "" Then
  Me.control8.SetFocus
  MsgBox "Please enter project Scope"
  Exit Sub
End If
'check for a Partners/Vendors
If Trim(Me.control9.Value) = "" Then
  Me.control9.SetFocus
  MsgBox "Please enter Partners/Vendors"
  Exit Sub
End If




'check for requestor
If Trim(Me.Control11.Value) = "" Then
  Me.Control11.SetFocus
  MsgBox "Please enter a Requestor"
  Exit Sub
End If
'check for Proj Owner
If Trim(Me.Control12.Value) = "" Then
  Me.Control12.SetFocus
  MsgBox "Please enter Project Owner"
  Exit Sub
End If


'check for a project type
If Trim(Me.Control13.Value) = "" Then
  Me.Control13.SetFocus
  MsgBox "Please select a Project Type"
  Exit Sub
End If


'check for a project category
If Trim(Me.Control14.Value) = "" Then
  Me.Control14.SetFocus
  MsgBox "Please select a Project Category"
  Exit Sub
End If


'check for risks
If Trim(Me.Control15.Value) = "" Then
  Me.Control15.SetFocus
  MsgBox "Please enter Risks"
  Exit Sub
End If


'check for a bus impact
If Trim(Me.Control16.Value) = "" Then
  Me.Control16.SetFocus
  MsgBox "Please enter Business Impact"
  Exit Sub
End If


'check for a bus value
If Trim(Me.Control17.Value) = "" Then
  Me.Control17.SetFocus
  MsgBox "Please enter Business Value"
  Exit Sub
End If


'check for a bus process impact
If Trim(Me.Control18.Value) = "" Then
  Me.Control18.SetFocus
  MsgBox "Please select value for Business Process Impact"
  Exit Sub
End If


'check for compliance
If Trim(Me.Control19.Value) = "" Then
  Me.Control19.SetFocus
  MsgBox "Please select value for Compliance"
  Exit Sub
End If




'check for cost savings
If Trim(Me.Control20.Value) = "" Then
  Me.Control20.SetFocus
  MsgBox "Please select value for Cost Savings"
  Exit Sub
End If




'check for CSAT
If Trim(Me.Control21.Value) = "" Then
  Me.Control21.SetFocus
  MsgBox "Please select value for CSAT"
  Exit Sub
End If


'check for Customer Impact
If Trim(Me.Control22.Value) = "" Then
  Me.Control22.SetFocus
  MsgBox "Please select value for Customer Impact"
  Exit Sub
End If


'check for ESAT
If Trim(Me.Control23.Value) = "" Then
  Me.Control23.SetFocus
  MsgBox "Please select value for ESAT"
  Exit Sub
End If


'check for New Product
If Trim(Me.Control24.Value) = "" Then
  Me.Control24.SetFocus
  MsgBox "Please select value for New Product/Innovation"
  Exit Sub
End If


'check for Revenue Growth
If Trim(Me.Control25.Value) = "" Then
  Me.Control25.SetFocus
  MsgBox "Please select value for Revenue Growth"
  Exit Sub
End If


'check for Complexity
If Trim(Me.Control26.Value) = "" Then
  Me.Control26.SetFocus
  MsgBox "Please select value for Complexity"
  Exit Sub
End If


'check for Technical Architecture
If Trim(Me.Control27.Value) = "" Then
  Me.Control27.SetFocus
  MsgBox "Please select value for Technical Architecture"
  Exit Sub
End If


'check for a est start date
If Trim(Me.Control28.Value) = "" Then
  Me.Control28.SetFocus
  MsgBox "Please select Estimated Start Date"
  Exit Sub
End If
'check for a est end date
If Trim(Me.Control29.Value) = "" Then
  Me.Control29.SetFocus
  MsgBox "Please select Estimated Finish Date"
  Exit Sub
End If


'check for a priority
If Trim(Me.Control30.Value) = "" Then
  Me.Control30.SetFocus
  MsgBox "Please select priority"
  Exit Sub
End If




'check for Cap Total
If Trim(Me.Control41.Value) = "" Then
  Me.Control30.SetFocus
  MsgBox "Please enter values for Capital Costs. If zero, enter 0"
  Exit Sub
End If




'check for Exp Total
If Trim(Me.Control42.Value) = "" Then
  Me.Control42.SetFocus
  MsgBox "Please enter values for Opex Costs. If zero, enter 0"
  Exit Sub
End If


'check for Exp Total
If Trim(Me.Control43.Value) = "" Then
  Me.Control43.SetFocus
  MsgBox "Please enter values for both Capital and Opex Costs."
  Exit Sub
End If


'check for a Impact Analysis
If Trim(Me.Control46.Value) = "" Then
  Me.Control46.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If


'check for a Impact Analysis
If Trim(Me.Control47.Value) = "" Then
  Me.Control47.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If




'check for a Impact Analysis
If Trim(Me.Control48.Value) = "" Then
  Me.Control48.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If




'check for a Impact Analysis
If Trim(Me.Control49.Value) = "" Then
  Me.Control49.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If




'check for a Impact Analysis
If Trim(Me.Control50.Value) = "" Then
  Me.Control50.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If




'check for a Impact Analysis
If Trim(Me.Control51.Value) = "" Then
  Me.Control51.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If






'check for a Impact Analysis
If Trim(Me.Control52.Value) = "" Then
  Me.Control52.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If


'check for a Impact Analysis
If Trim(Me.Control53.Value) = "" Then
  Me.Control53.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If


'check for a Impact Analysis
If Trim(Me.Control54.Value) = "" Then
  Me.Control54.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If


'check for a Impact Analysis
If Trim(Me.Control55.Value) = "" Then
  Me.Control55.SetFocus
  MsgBox "Please select True or False for impact on all divisions listed"
  Exit Sub
End If


'check for a New product
If Trim(Me.Control62.Value) = "" Then
  Me.Control62.SetFocus
  MsgBox "Please select value for New Product"
  Exit Sub
End If


'check for a Capital Cost Avoidance
If Trim(Me.control63.Value) = "" Then
  Me.control63.SetFocus
  MsgBox "Please select value for Capital Cost Avoidance"
  Exit Sub
End If


'check for Asset Type
If Trim(Me.Control64.Value) = "" Then
  Me.Control64.SetFocus
  MsgBox "Please select value for Asset Type"
  Exit Sub
End If


'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(lRow, 1).Value = Me.Control1.Value
  .Cells(lRow, 2).Value = Me.Control2.Value
  .Cells(lRow, 3).Value = Me.Control3.Value
  .Cells(lRow, 4).Value = Me.Control4.Value
  .Cells(lRow, 5).Value = Me.Control5.Value
  .Cells(lRow, 6).Value = Me.Control6.Value
  .Cells(lRow, 7).Value = Me.Control7.Value
  .Cells(lRow, 8).Value = Me.control8.Value
  .Cells(lRow, 9).Value = Me.control9.Value
  .Cells(lRow, 10).Value = Me.Control10.Value
  .Cells(lRow, 11).Value = Me.Control11.Value
  .Cells(lRow, 12).Value = Me.Control12.Value
  .Cells(lRow, 13).Value = Me.Control13.Value
  .Cells(lRow, 14).Value = Me.Control14.Value
  .Cells(lRow, 15).Value = Me.Control15.Value
  .Cells(lRow, 16).Value = Me.Control16.Value
  .Cells(lRow, 17).Value = Me.Control17.Value
  .Cells(lRow, 18).Value = Me.Control18.Value
  .Cells(lRow, 19).Value = Me.Control19.Value
  .Cells(lRow, 20).Value = Me.Control20.Value
  .Cells(lRow, 21).Value = Me.Control21.Value
  .Cells(lRow, 22).Value = Me.Control22.Value
  .Cells(lRow, 23).Value = Me.Control23.Value
  .Cells(lRow, 24).Value = Me.Control24.Value
  .Cells(lRow, 25).Value = Me.Control25.Value
  .Cells(lRow, 26).Value = Me.Control26.Value
  .Cells(lRow, 27).Value = Me.Control27.Value
  .Cells(lRow, 28).Value = Me.Control28.Value
  .Cells(lRow, 29).Value = Me.Control29.Value
  .Cells(lRow, 30).Value = Me.Control30.Value
  .Cells(lRow, 31).Value = Me.Control31.Value
  .Cells(lRow, 32).Value = Me.Control32.Value
  .Cells(lRow, 33).Value = Me.Control33.Value
  .Cells(lRow, 34).Value = Me.Control34.Value
  .Cells(lRow, 35).Value = Me.Control35.Value
  .Cells(lRow, 36).Value = Me.Control36.Value
  .Cells(lRow, 37).Value = Me.Control37.Value
  .Cells(lRow, 38).Value = Me.Control38.Value
  .Cells(lRow, 39).Value = Me.Control39.Value
  .Cells(lRow, 40).Value = Me.control40.Value
  .Cells(lRow, 41).Value = Me.Control41.Value
  .Cells(lRow, 42).Value = Me.Control42.Value
  .Cells(lRow, 46).Value = Me.Control46.Value
  .Cells(lRow, 47).Value = Me.Control47.Value
  .Cells(lRow, 48).Value = Me.Control48.Value
  .Cells(lRow, 49).Value = Me.Control49.Value
  .Cells(lRow, 50).Value = Me.Control50.Value
  .Cells(lRow, 51).Value = Me.Control51.Value
  .Cells(lRow, 52).Value = Me.Control52.Value
  .Cells(lRow, 53).Value = Me.Control53.Value
  .Cells(lRow, 54).Value = Me.Control54.Value
  .Cells(lRow, 55).Value = Me.Control55.Value
  .Cells(lRow, 56).Value = Me.Control56.Value
  .Cells(lRow, 57).Value = Me.Control57.Value
  .Cells(lRow, 58).Value = Me.Control58.Value
  .Cells(lRow, 59).Value = Me.Control59.Value
  .Cells(lRow, 60).Value = Me.control60.Value
  .Cells(lRow, 61).Value = Me.Control61.Value
  .Cells(lRow, 62).Value = Me.Control62.Value
  .Cells(lRow, 63).Value = Me.control63.Value
  .Cells(lRow, 64).Value = Me.Control64.Value
'  .Protect Password:="password"
End With


'clear the data
Me.Control1.Value = ""
Me.Control2.Value = ""
Me.Control3.Value = ""
Me.Control4.Value = ""
Me.Control5.Value = ""
Me.Control6.Value = ""
Me.Control7.Value = ""
Me.control8.Value = ""
Me.control9.Value = ""
Me.Control10.Value = ""
Me.Control11.Value = ""
Me.Control12.Value = ""
Me.Control13.Value = ""
Me.Control14.Value = ""
Me.Control15.Value = ""
Me.Control16.Value = ""
Me.Control17.Value = ""
Me.Control18.Value = ""
Me.Control19.Value = ""
Me.Control20.Value = ""
Me.Control21.Value = ""
Me.Control22.Value = ""
Me.Control23.Value = ""
Me.Control24.Value = ""
Me.Control25.Value = ""
Me.Control26.Value = ""
Me.Control27.Value = ""
Me.Control28.Value = ""
Me.Control29.Value = ""
Me.Control30.Value = ""
Me.Control31.Value = ""
Me.Control32.Value = ""
Me.Control33.Value = ""
Me.Control34.Value = ""
Me.Control35.Value = ""
Me.Control36.Value = ""
Me.Control37.Value = ""
Me.Control38.Value = ""
Me.Control39.Value = ""
Me.control40.Value = ""
Me.Control41.Value = ""
Me.Control42.Value = ""
Me.Control43.Value = ""
Me.Control44.Value = ""
Me.Control45.Value = ""
Me.Control46.Value = ""
Me.Control47.Value = ""
Me.Control48.Value = ""
Me.Control49.Value = ""
Me.Control50.Value = ""
Me.Control51.Value = ""
Me.Control52.Value = ""
Me.Control53.Value = ""
Me.Control54.Value = ""
Me.Control55.Value = ""
Me.Control56.Value = ""
Me.Control57.Value = ""
Me.Control58.Value = ""
Me.Control59.Value = ""
Me.control60.Value = ""
Me.Control61.Value = ""
Me.Control62.Value = ""
Me.control63.Value = ""
Me.Control64.Value = ""


End Sub


Private Sub ComboBox2_Change()


End Sub


Private Sub triggerCombo_Change()


End Sub


Private Sub cmdClear_Click()
Control1.Value = ""
Control2.Value = ""
Control3.Value = ""
Control4.Value = ""
Control5.Value = ""
Control6.Value = ""
Control7.Value = ""
control8.Value = ""
control9.Value = ""
Control10.Value = ""
Control11.Value = ""
Control12.Value = ""
Control13.Value = ""
Control14.Value = ""
Control15.Value = ""
Control16.Value = ""
Control17.Value = ""
Control18.Value = ""
Control19.Value = ""
Control20.Value = ""
Control21.Value = ""
Control22.Value = ""
Control23.Value = ""
Control24.Value = ""
Control25.Value = ""
Control26.Value = ""
Control27.Value = ""
Control28.Value = ""
Control29.Value = ""
Control30.Value = ""
Control31.Value = ""
Control32.Value = ""
Control33.Value = ""
Control34.Value = ""
Control35.Value = ""
Control36.Value = ""
Control37.Value = ""
Control38.Value = ""
Control39.Value = ""
control40.Value = ""
Control41.Value = ""
Control42.Value = ""
Control43.Value = ""
Control44.Value = ""
Control45.Value = ""
Control46.Value = ""
Control47.Value = ""
Control48.Value = ""
Control49.Value = ""
Control50.Value = ""
Control51.Value = ""
Control52.Value = ""
Control53.Value = ""
Control54.Value = ""
Control55.Value = ""
Control56.Value = ""
Control57.Value = ""
Control58.Value = ""
Control59.Value = ""
control60.Value = ""
Control61.Value = ""
Control62.Value = ""
control63.Value = ""
Control64.Value = ""


End Sub


Private Sub cmdClose_Click()
'close the userform
Unload Me
End Sub




Private Sub cmdResizeListBox_Click()
  Control12.Height = 120 - Control12.Height
End Sub


Private Sub CommandButton1_Click()


End Sub


Private Sub CommandButton3_Click()


End Sub










Private Sub Control12_Click()


End Sub
Dim Currentrow As Long


Private Sub cmdDeleteButton_Click()




End Sub


Private Sub CmdNext_Click()


Dim n As Integer


If Sheets("MiscFormulas").Range("B4") < Sheets("MiscFormulas").Range("B5") Then
n = Sheets("MiscFormulas").Range("B4") + 1
Sheets("MiscFormulas").Range("B4") = n
Call update
Else
    MsgBox ("You are at the last record!")
End If


End Sub


Private Sub cmdPrevious_Click()
Dim n As Integer


If Sheets("MiscFormulas").Range("B4") > 1 Then
n = Sheets("MiscFormulas").Range("B4") - 1
Sheets("MiscFormulas").Range("B4") = n
Call update
Else
    MsgBox ("You are at the first record!")
End If


End Sub


Private Sub cmdUpdate_Click()






End Sub


Private Sub Control1_Change()


End Sub


Private Sub Control10_Change()


End Sub


Private Sub txtSearch_Change()


End Sub


Private Sub Control13_Change()


End Sub


Private Sub Control18_Change()


End Sub


Private Sub Control2_Change()


End Sub


Private Sub Control20_Change()


End Sub


Private Sub Control24_Change()


End Sub


Private Sub Control26_Change()


End Sub


Private Sub Control27_Change()


End Sub


Private Sub Control28_Change()


End Sub


Private Sub Control29_Change()


End Sub


Private Sub Control31_Change()


End Sub


Private Sub Control4_Change()




Dim index As Integer
 index = Control4.ListIndex


 Control5.Clear


Select Case index
     Case Is = 0
         With Control5
             .AddItem "Client Services"
             .AddItem "Compute"
             .AddItem "Data Center"
             .AddItem "Information Security"
             .AddItem "Network"
             .AddItem "Platform"
             .AddItem "Storage"
             .AddItem "Technology Refresh"
             .AddItem "Unified Communications"
             .AddItem "Work Place Platform"
                 
                        
         End With
     Case Is = 1
         With Control5
             .AddItem "Universal Orlando"
             .AddItem "Universal Parks & Resorts"
             .AddItem "Universal Studios Hollywood"
             .AddItem "Universal Parks Technology"
             .AddItem "Other"
         End With
     Case Is = 2
         With Control5
             .AddItem "subportfolioA1"
             .AddItem "subportfolioA2"
             .AddItem "subportfolioA3"
         End With
    Case Is = 3
         With Control5
             .AddItem "subportfolioB1"
             .AddItem "subportfolioB2"
             .AddItem "subportfolioB3"
         End With


 
         
End Select
 
End Sub


Private Sub control40_Change()


End Sub


Private Sub Control41_Change()


End Sub


Private Sub Control42_Change()


End Sub


Private Sub Control43_Change()
Control43.Text = Val(Control31.Text) + Val(Control32.Text) + Val(Control33.Text) + Val(Control34.Text) + Val(Control35.Text) + Val(Control36.Text)


End Sub


Private Sub Control44_Change()
Control44.Text = Val(Control37.Text) + Val(Control38.Text) + Val(Control39.Text) + Val(control40.Text) + Val(Control41.Text) + Val(Control42.Text)
End Sub


Private Sub Control45_Change()
Control45.Text = Val(Control43.Text) + Val(Control44.Text)
End Sub


Private Sub Control5_Change()


End Sub


Private Sub Control55_Change()


End Sub


Private Sub control60_Change()


End Sub


Private Sub Frame1_Click()


End Sub


Private Sub Frame3_Click()


End Sub


Private Sub Frame4_Click()


End Sub


Private Sub Frame5_Click()


End Sub


Private Sub Label11_Click()


End Sub


Private Sub Label14_Click()


End Sub


Private Sub Label41_Click()


End Sub


Private Sub Label47_Click()


End Sub


Private Sub Label68_Click()


End Sub


Private Sub UserForm_Activate()


End Sub


Sub update()
'to update the form


Control1.Value = Sheets("MiscFormulas").Range("A1")
Control2.Value = Sheets("MiscFormulas").Range("B1")
Control3.Value = Sheets("MiscFormulas").Range("C1")
Control4.Value = Sheets("MiscFormulas").Range("d1")
Control5.Value = Sheets("MiscFormulas").Range("E1")
Control6.Value = Sheets("MiscFormulas").Range("F1")
Control7.Value = Sheets("MiscFormulas").Range("G1")
control8.Value = Sheets("MiscFormulas").Range("H1")
control9.Value = Sheets("MiscFormulas").Range("I1")
Control10.Value = Sheets("MiscFormulas").Range("J1")
Control11.Value = Sheets("MiscFormulas").Range("K1")
Control12.Value = Sheets("MiscFormulas").Range("L1")
Control13.Value = Sheets("MiscFormulas").Range("M1")
Control14.Value = Sheets("MiscFormulas").Range("N1")
Control15.Value = Sheets("MiscFormulas").Range("O1")
Control16.Value = Sheets("MiscFormulas").Range("P1")
Control17.Value = Sheets("MiscFormulas").Range("Q1")
Control18.Value = Sheets("MiscFormulas").Range("R1")
Control19.Value = Sheets("MiscFormulas").Range("S1")
Control20.Value = Sheets("MiscFormulas").Range("T1")
Control21.Value = Sheets("MiscFormulas").Range("U1")
Control22.Value = Sheets("MiscFormulas").Range("V1")
Control23.Value = Sheets("MiscFormulas").Range("W1")
Control24.Value = Sheets("MiscFormulas").Range("X1")
Control25.Value = Sheets("MiscFormulas").Range("Y1")
Control26.Value = Sheets("MiscFormulas").Range("Z1")
Control27.Value = Sheets("MiscFormulas").Range("AA1")
Control28.Value = Sheets("MiscFormulas").Range("AB1")
Control29.Value = Sheets("MiscFormulas").Range("AC1")
Control30.Value = Sheets("MiscFormulas").Range("AD1")
Control31.Value = Sheets("MiscFormulas").Range("AE1")
Control32.Value = Sheets("MiscFormulas").Range("AF1")
Control33.Value = Sheets("MiscFormulas").Range("AG1")
Control34.Value = Sheets("MiscFormulas").Range("AH1")
Control35.Value = Sheets("MiscFormulas").Range("AI1")
Control36.Value = Sheets("MiscFormulas").Range("AJ1")
Control37.Value = Sheets("MiscFormulas").Range("AK1")
Control38.Value = Sheets("MiscFormulas").Range("AL1")
Control39.Value = Sheets("MiscFormulas").Range("AM1")
control40.Value = Sheets("MiscFormulas").Range("AN1")
Control41.Value = Sheets("MiscFormulas").Range("AO1")
Control42.Value = Sheets("MiscFormulas").Range("AP1")
Control43.Value = Sheets("MiscFormulas").Range("AQ1")
Control44.Value = Sheets("MiscFormulas").Range("AR1")
Control45.Value = Sheets("MiscFormulas").Range("AS1")
Control46.Value = Sheets("MiscFormulas").Range("AT1")
Control47.Value = Sheets("MiscFormulas").Range("AU1")
Control48.Value = Sheets("MiscFormulas").Range("AV1")
Control49.Value = Sheets("MiscFormulas").Range("AW1")
Control50.Value = Sheets("MiscFormulas").Range("AX1")
Control51.Value = Sheets("MiscFormulas").Range("AY1")
Control52.Value = Sheets("MiscFormulas").Range("AZ1")
Control53.Value = Sheets("MiscFormulas").Range("BA1")
Control54.Value = Sheets("MiscFormulas").Range("BB1")
Control55.Value = Sheets("MiscFormulas").Range("BC1")
Control56.Value = Sheets("MiscFormulas").Range("BD1")
Control57.Value = Sheets("MiscFormulas").Range("BE1")
Control58.Value = Sheets("MiscFormulas").Range("BF1")
Control59.Value = Sheets("MiscFormulas").Range("BG1")
control60.Value = Sheets("MiscFormulas").Range("BH1")
Control61.Value = Sheets("MiscFormulas").Range("BI1")
Control62.Value = Sheets("MiscFormulas").Range("BJ1")
control63.Value = Sheets("MiscFormulas").Range("BK1")
Control64.Value = Sheets("MiscFormulas").Range("BL1")


End Sub


Private Sub UserForm_Initialize()


Currentrow = 2
Control1.Text = Cells(Currentrow, 1).Text
Control2.Text = Cells(Currentrow, 2).Text


End Sub


Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)


End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
If it were me, I'd move ProjectData sheet updates into its own procedure. That that you can call it from either the cmdAdd procedure or an update procedure just by referencing the row number. You can also do the same thing for clearing the form - I see you've mirrored the code in two places. So, after you complete all the form validations in the comAdd procedure, you would replace the remaining lines with something like


call populate_data(lRow)

call clearForm
Exit sub

Hope this helps.


Tim



Hope this helps.


Tim
 
Upvote 0
Tim's comments worth noting reducing repeating code makes project much easier to read & debug.

Are you able to place copy of your workbook in a dropbox with sample data?

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

Similar threads

J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
326
Rhodie72
Rhodie72
A
  • Solved
how should show number form in label on userform
Replies
3
Views
172
Joe4
Joe4
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
192
ipbr21054
I
I
  • Question Question
Worksheet value is updated only after userform is closed
2
Replies
13
Views
482
ipbr21054
I
K
  • Question Question
How to shorten vba code without any problems
Replies
1
Views
262
kamranyd
K

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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