Adding records to a multi-sheet database

DrGuru · Jun 22nd 2005

Hello,

It's been a couple of years since I was last on here, but thought I'd come back before doing something rash, like throwing my system out the window.

I am setting up a database in Excel for one of my businesses. This database has multiple worksheets, one for each business category.

Basically, I am trying to design a simple userform that will allow me to add records to the registry. The userform contains three labels, two textboxes, a combobox, and two command buttons.

The combobox is how I select the business category that the new entry needs to be entered in. What I am trying to do is when I enter the data on the form and select the category, then when I click the Add Record button, it will automatically go to the worksheet for that business category, find the next empty row and paste the data from the form into the appropriate cells.

I would post a copy of the workbook except that it is quite large already (over 1MB). Any assistance the could be rendered would be most appreciated. Oh, also, not sure if you need to know, but I am using Excel 2003.

Will Riley · Jun 22nd 2005

Re: Adding records to a multi-sheet database

I had a bit of time on my hands so i created this for you

Code

Private Sub CmbPaste_Click()
Dim stWs As String, lrow As Long
Dim ws As Worksheet


stWs = Me.ComboBox1.Text
Set ws = ThisWorkbook.Worksheets(stWs)


With ws
    lrow = .Range("A65536").End(xlUp).Offset(1, 0).Row
    .Cells(lrow, 1) = Me.TextBox1
    .Cells(lrow, 2) = Me.TextBox2
    .Cells(lrow, 3) = Me.TextBox3
    .Cells(lrow, 4) = Me.TextBox4
    .Cells(lrow, 5) = Me.TextBox5
End With
Unload Me
End Sub

Display More

to load the sheets into the combo

Code

Private Sub UserForm_Initialize()
'add all the sheets in the workbook to the form combo
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ComboBox1.AddItem ws.Name
Next
End Sub

See attached eg file

DrGuru · Jun 23rd 2005

Re: Adding records to a multi-sheet database

My most sincere thanks for your assistance in this. You have saved me hours of work as a lot of what I am doing is getting information from a business registration site, and when I would paste from the site directly into a cell, it would inherit the hyperlink, which is what I didn't need nor want. Any given that I have just under 200 worksheets to work with so far, having to scroll through them all would have taken a great deal of time.

So again, I thank you.

Adding records to a multi-sheet database

Participate now!

Share