Moving Data from Userform to Database

[petro62]

So I have a user form that has ~60 entry fields. These are spread over 7 tabs in the userform.

What is the most efficient way to copy this over to the database? Currently on the last tab I have a save button and when that is clicked it inserts a new row on the database and starts to fill it in. I was currently typing out each line where it goes and how much offset it needs. I was not sure if there was a more efficient way to code this.

Secondly,
In some of the comboboxes and text boxes I have a default description of what goes into that box. Is there a way that when the user clicks or starts typing in the box it clears that default? And if it is left blank it keeps that default?

Last,
I also want a function to click and Edit button the database sheet. That will bring up a form with a dropdown box of showing all of the data in one column so they can select the one they want, click it and then it opens the original userform so they can edit it (instead of create new).


I attached an image of what one tab of the user form looks like and a section of the VBA on how I am copying the information over(still a work in progress as you can tell by all the lines with the same name)

Private Sub Finish_Click()
'Offset(row,column)

Range("A4").EntireRow.Insert
Sheets("Projects").Range("DataStart").Offset(1, 0) = ProjectNumber
Sheets("Projects").Range("DataStart").Offset(1, 1) = ProjectName
Sheets("Projects").Range("DataStart").Offset(1, 2) = ProjectStatus
Sheets("Projects").Range("DataStart").Offset(1, 3) = ProjectArea
Sheets("Projects").Range("DataStart").Offset(1, 4) = ProjectType
Sheets("Projects").Range("DataStart").Offset(1, 5) = ProjectCM
Sheets("Projects").Range("DataStart").Offset(1, 6) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 7) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 8) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 9) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 10) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 11) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 12) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 13) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 14) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 15) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 16) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 17) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 19) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 20) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 22) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 25) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 26) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 27) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 28) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 29) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 30) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 31) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 32) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 33) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 34) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 35) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 36) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 37) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 38) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 39) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 40) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 41) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 42) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 43) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 44) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 45) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 46) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 47) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 48) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 49) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 50) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 51) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 52) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 53) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 54) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 55) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 56) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 57) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 58) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 59) = ProjectPhase
Sheets("Projects").Range("DataStart").Offset(1, 60) = ProjectPhase


End Sub

 

[DanteAmor]

What is the most efficient way to copy this over to the database? Currently on the last tab I have a save button and when that is clicked it inserts a new row on the database and starts to fill it in. I was currently typing out each line where it goes and how much offset it needs. I was not sure if there was a more efficient way to code this.

Secondly,
In some of the comboboxes and text boxes I have a default description of what goes into that box. Is there a way that when the user clicks or starts typing in the box it clears that default? And if it is left blank it keeps that default?

Try this:

Option Explicit

Const Text_PjName = "N.### for App & O.### for AEs"

Private Sub Finish_Click()
  Dim sh As Worksheet
  Dim arr As Variant, ar2 As Variant, ar3 As Variant
  Dim i As Long
  
  Set sh = Sheets("Projects")
  arr = Array("", ProjectNumber, ProjectName, ProjectStatus, ProjectArea, ProjectType, ProjectCM, _
              ProjectPhase, PjEwp, PjPq, PjMat, PjDev, PjDes, _
              "Continue with the controls in the order of the columns")

  sh.Range("A4").EntireRow.Insert
  With sh.Range("DataStart")
    For i = 1 To UBound(arr)
      Select Case TypeName(arr(i))
        Case "OptionButton"
          If arr(i) Then .Cells(4, i).Value = "Yes" Else .Cells(4, i).Value = "No"
        Case "If you have other types of controls"
          'instructions
        Case Else   'combobox and textbox
          .Cells(4, i).Value = arr(i)
        End Select
    Next
  End With
End Sub

Private Sub ProjectNumber_Enter()
  With ProjectNumber
    If .Value = Text_PjName Then .Value = ""
  End With
End Sub

Private Sub ProjectNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  With ProjectNumber
    If .Value = "" Then .Value = Text_PjName
  End With
End Sub

Private Sub UserForm_Activate()
  ProjectNumber.Value = Text_PjName
End Sub

 

[dmt32]

What is the most efficient way to copy this over to the database?

such a large project with so many user input controls on one form my personal suggestion would be to avoid hard coding the controls rather, export them to a table in a worksheet which can be used to read in to an array. This array once initialized, can be used throughout your project & will streamline your codes.

In some of the comboboxes and text boxes I have a default description of what goes into that box.

Is there a way that when the user clicks or starts typing in the box it clears that default? And if it is left blank it keeps that default?

This can be done by use of couple of the textbox controls events

example

Const DefaultText As String = "Enter Short Description"

Private Sub ProjectDescription_Enter()

With Me.ProjectDescription

If .Value = DefaultText Then .Value = ""

End With

End Sub

Private Sub ProjectDescription_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With Me.ProjectDescription

If Len(.Value) = 0 Then .Value = DefaultText

End With

End Sub

Private Sub UserForm_Initialize()

Me.ProjectDescription.Value = DefaultText

End Sub

Note the Const statement with your message – This must sit at top of your forms code page outside any procedure.

I also want a function to click and Edit button the database sheet.

Personally, I would avoid having separate search form – You could either just use one of the existing controls (like Project No) to enter a value in & press Find button or just add a separate search textbox to do same thing. If really wanted, you could also add a listbox that behaves like a dropdown so as user types in search box you get a list of selection choices – this though involves some extensive coding.

If need further assistance helpful to forum if can with such complex project, place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it - plenty here to offer guidance.

Hope Helpful



Dave

 

[petro62]

A little delayed, but as far as the Edit function goes I mean it would work like this. On the work sheet they would click the edit button instead of Add New. This would pull up a form that consist of 2 inputs (Project Status Filter & Project Manager Filter) and then a dropdown box that would populate based on those filters. After they selected the project they wanted in the drop down they would then click an edit button again and they would be taken back to the original Add new userform, but everything would be populated so they could easily update/edit. I hope that makes sense.

I also want a function to click and Edit button the database sheet.

Personally, I would avoid having separate search form – You could either just use one of the existing controls (like Project No) to enter a value in & press Find button or just add a separate search textbox to do same thing. If really wanted, you could also add a listbox that behaves like a dropdown so as user types in search box you get a list of selection choices – this though involves some extensive coding.

If need further assistance helpful to forum if can with such complex project, place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it - plenty here to offer guidance.

 

[dmt32]

You can perform all the required operations from the same userform.
I should have also added that if its your intention to create a database have you considered looking at MS Access? which is better suited & much easier to work with. There are plenty of experts on the Access forum that can offer guidance.

If you intend to continue with excel then to repeat what I said with such a complex project, it would help forum if you place a copy of workbook on file sharing site like dropbox & provide link to otherwise, contributors can only guess with their suggestions.

Dave

 

[Joe4]

You can perform all the required operations from the same userform.
I should have also added that if its your intention to create a database have you considered looking at MS Access? which is better suited & much easier to work with. There are plenty of experts on the Access forum that can offer guidance.

If you intend to continue with excel then to repeat what I said with such a complex project, it would help forum if you place a copy of workbook on file sharing site like dropbox & provide link to otherwise, contributors can only guess with their suggestions.

Dave

I agree with Dave's assessment. Being a relational database program, Access is better suited for this than Excel is.
And all things being equal, I find Access's form SO MUCH EASIER to work with than Excel User Forms.
Since you can "bind" the form to the underlying table, you don't need to use VBA to map each field from your form to the data source.

 

[petro62]

You can perform all the required operations from the same userform.
I should have also added that if its your intention to create a database have you considered looking at MS Access? which is better suited & much easier to work with. There are plenty of experts on the Access forum that can offer guidance.

If you intend to continue with excel then to repeat what I said with such a complex project, it would help forum if you place a copy of workbook on file sharing site like dropbox & provide link to otherwise, contributors can only guess with their suggestions.

Dave

Thanks both for the suggestions. I am not sure if Access is an option just do to our corporate standard, but I may be able to because at the end of the day it will have the same information. And graphically I just pull most of this into powerbi. As I think about it sounds like I need to readup on access and how to create a database and userform in there to see if I can keep it all cleaner.

 

[dmt32]

There are pros & cons as to which application is better suited to a task and although there is a bit more of a learning curve with access unlike Excel, databases in Access are related.

This makes it is easy to store information in one place and refer to it in other places. A table can simply be referenced in other tables and when the date changes, it automatically updates in all the referenced places.

Worksheets in Excel are flat which means they are not related at multiple levels. Data in Excel is connected by their rows and columns but without creative VBA, that is it and whilst even for a novice, it is relativity easy to design & create a userform interface in Excel, making it work can require a substantial amount of sometimes complex coding which even some experienced users have problems with.

If a database is your aim, do put the effort in to understand Access you will be glad you did & if need help, Access forum here to guide you but if still intend to pursue project in Excel, sharing copy of your workbook would be helpful to forum.

Dave

 

[Joe4]

I echo everything that Dave said there.

IMO, the biggest key in designing an Access database is to set up the tables correctly. If you set up the tables according to the first few Rules of Normalization, it will allow you to do pretty much whatever you want without great effort. If your tables are not normalized, you will deal with lots of headaches and frustration (I found out myself the hard way the first time I created an Access database!).

There are lots of great write-ups on Access Database Design and Normalization on the internet. Here is one that talks about Normalization:

Database normalization description - Microsoft 365 Apps

Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.

docs.microsoft.com

 

[petro62]

Thank you both again. I will investigate access and see if I can accomplish what I need there. I know it is capable of holding all the data I guess I was just thinking more visual, but I will read up on it and find out. You both have been super helpful.