I am very new to VBA and would use Access for what I need except they do not allow Access database use at work. I cannot give details of my work on this forum but I will try to use a scenario to explain my query. I am unable to attach the spreadsheet with an example because of restrictions on my computer. If someone can contact me privately i can send via my personal email address. I have also put the VBA code for each form below

I have a spreadsheet (businesses) which I populate using userform1 (businessform) which details aspects of a business. Column A already has pre-assigned reference numbers in it.

From the businessform I have a command button (add employee) which opens userform2 (employform) and populates another spreadsheet (employees) detailing the employers in the business, with each employee on its own row

I need to do three things:

1) I need the businessform to pick up the next pre-assigned reference from the businesses spreadsheet on Column A (the next available reference will be the first one in Column A with no entry in column B)
2) I need the employform to pick up the same pre-assigned reference from the businessform when I click the command button to open it so that the two are linked
3) I need the employform to keep the same pre-assigned reference from the businessform when I keep adding employees from it so that all employees are linked to the same business

If I had use of access I could put in a primary key and so a relationship between the business and the multiple employess could be maintained.


Please note that I have use of excel 2003.

Any help would be more than welcome!

VBA code for the business form is



Private Sub Addemployee_Click()
employform.Show
End Sub


Private Sub mustbehere_Change()

End Sub

Private Sub savebusinessandadd_Click()
Dim NextRow As Long

Sheets("businesses").Activate

NextRow = Application.WorksheetFunction.CountA(Range("B:B")) + 2


If heading1 Then Cells(NextRow, 3) = "x"

If heading2 Then Cells(NextRow, 4) = "x"

If heading3 Then Cells(NextRow, 5) = "x"

Cells(NextRow, 2) = mustbehere.Text

mustbehere.Text = " "

mustbehere.Text = SetFocus

Unload Me

businessform.Show
End Sub

Private Sub savebusinessandclose_Click()
Dim NextRow As Long

Sheets("businesses").Activate

NextRow = Application.WorksheetFunction.CountA(Range("B:B")) + 2


If heading1 Then Cells(NextRow, 3) = "x"

If heading2 Then Cells(NextRow, 4) = "x"

If heading3 Then Cells(NextRow, 5) = "x"

Cells(NextRow, 2) = mustbehere.Text

mustbehere.Text = " "

mustbehere.Text = SetFocus

Unload Me

businessform.Hide

End Sub

Private Sub UserForm_Click()

End Sub



VBA code for employform is


Private Sub saveaddemployee_Click()
Sheets("employees").Activate

NextRow = Application.WorksheetFunction.CountA(Range("b:b")) + 2

Cells(NextRow, 1) = referencebox.Text

If heading4 Then Cells(NextRow, 3) = "x"

If heading5 Then Cells(NextRow, 4) = "x"

If heading6 Then Cells(NextRow, 5) = "x"


referencebox.Text = " "

referencebox.Text = SetFocus

Cells(NextRow, 2) = mustbehere2.Text

mustbehere2.Text = " "

mustbehere2.Text = SetFocus

Unload Me

employform.Show


End Sub

Private Sub saveandclose_Click()

Sheets("employees").Activate

NextRow = Application.WorksheetFunction.CountA(Range("b:b")) + 2

Cells(NextRow, 1) = referencebox.Text

If heading4 Then Cells(NextRow, 3) = "x"

If heading5 Then Cells(NextRow, 4) = "x"

If heading6 Then Cells(NextRow, 5) = "x"

Cells(NextRow, 2) = mustbehere2.Text

mustbehere2.Text = " "

mustbehere2.Text = SetFocus

referencebox.Text = " "

referencebox.Text = SetFocus

Unload employform


End Sub