help with excel vba.....about saving the data from form on pressing ok in another excel file?

i have a form that has name and then has three check boxes highschool, college, graduate school

so to save the data in excel file i have used this code in vba:

Private Sub OKButton_Click()

Sheets("Sheet1").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cells(NextRow, 1) = TextName.Text

If OptionHS Then Cells(NextRow, 2) = "High School"

If OptionCollege Then Cells(NextRow, 3) = "College"

If OptionGrad Then Cells(NextRow, 4) = "Grad School"

TextName.Text = ""

TextName.SetFocus

End Sub

so that means on the sheet1 the data gets stored but what i want is that this data actually gets saved in SOME OTHER EXCEL FILE ON Sheet 1 ......lets say that both these excel files are in same folder

the excel file where people add data is named book1 and the excel file where i want to store data is named database

so in other words what i want to say is that these two files be on the shared drive....so i want that all people can open that form on their own screen and when lets say user1 adds data it gets saved in databse.....similarly when user 2 adds.....and so on .....i hope i make sense.....

i will choose best answer inmmediately

please help

Update:

That is such a helpful thing for u to do ....please help me because i m getting syntax error

Lets Say the excel sheet where i have the form is on this path:

C:\Users\abc\Desktop\Book1.xls

and the database is here:

C:\Users\abc\Desktop\Mumbai Centre Dashboard\databse.xls

so can you please put that in the code and then tell me how can i do what i want....thanks in advance

1 Answer

Relevance
  • 1 decade ago
    Favorite Answer

    If your macro simply does a "Save As" of Book1.xls and overwrites Database.xls, you would lose any previously stored data in Database.xls

    What I think you're asking is to ADD the data from your user form to Database.xls and not overwrite it. Is that correct? If yes, one problem is that the "NextRow" is not the same in Book1.xls as it would be in Database.xls. I would recomend that the macro opens up Database.xls (and not show it to the user) and adds the data to the "NextRow" in Database.xls and save and closes it.

    Private Sub OKButton_Click()

    Dim NextRow As Long

    Dim wbDatabase As Workbook

    Dim sPath As String

    'Check if Name is filled in

    If TextName = "" Then

    MsgBox "Please enter your name and select an education level."

    TextName.SetFocus

    Exit Sub

    End If

    Sheets("Sheet1").Activate

    NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

    Cells(NextRow, 1) = TextName.Text

    If OptionHS Then Cells(NextRow, 2) = "High School"

    If OptionCollege Then Cells(NextRow, 3) = "College"

    If OptionGrad Then Cells(NextRow, 4) = "Grad School"

    'Open Database.xls and add data. Then save and close

    sPath = ThisWorkbook.Path & "\" 'File path to open\save

    Set wbDatabase = Workbooks.Open(sPath & "Database.xls")

    With wbDatabase.Sheets("Sheet1")

    NextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

    .Cells(NextRow, 1) = TextName.Text

    If OptionHS Then .Cells(NextRow, 2) = "High School"

    If OptionCollege Then .Cells(NextRow, 3) = "College"

    If OptionGrad Then .Cells(NextRow, 4) = "Grad School"

    End With

    wbDatabase.Save

    wbDatabase.Close

    TextName.Text = ""

    TextName.SetFocus

    End Sub

Still have questions? Get your answers by asking now.