Create a user control form in Excel 2010

[--MasseySian--]

I want to create database in Excel 2010 such that it will consist of inbuilt Windows form. It will have textboxes with labels to mention their purpose. It should work in a way such that data that I enter in the Windows form should be inserted in the Excel 2010 document according to the column name. Also it should generate data automatically before taking in the entry every day. Can someone help me with it?

[Holbrook]

You can use the macros in Excel 2010 to get such a thing in your document. I don’t have much idea about creati8ng Windows form in the Excel 2010. I have been using Visual Basic 2012 to create Windows form and then connecting it to Excel 2010. Also for the purpose of the date you will need to use macros. One thing I would like to say about Excel 2010 if you are going to use it as database. You need to fill every field to get the entry right without any error. So you need to make another macro to fill the empty spaces with some random value.

[Faizah]

I have designed a macro to have a database in Excel 2010. The forms will have to be designed by you according to your need. Here is the code for creating database in Excel 2010,

Application.ScreenUpdating = False
Set quotetracking = Workbooks.Open( ActiveWorkbook.Path & "\yourexceldoc.xls", _
False, True)
ListItems = quotetracking.quotelist(1).Range("B2:B21").Value
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
ListItems = Application.WorksheetFunction.Transpose(ListItems)

The place where I have typed ‘yourexceldoc.xls’, replace it with the name of the file you have created.

[Mattabesic]

If you are using more than one document for same interface or maybe there is relation between multiple files, then editing each of them individually would become much hectic. You can use this code to merge multiple files to work in integrity. Here is the code for it,

Public Fileway As String
Private Sub UserForm_Activate()
Fileway = ActiveWorkbook.Path
Me.Caption = Me.Caption & "Filepath:" & Fileway
End Sub

When using this insert names of the files that you want to use as database. Put the name with extension in place of Filepath: that I have marked with red color.