VBA form question

[mml]

Hello all I have a general question which is
Can you design a data entry form that places the data into a specific labeled worksheet (tab) in a shared work book .
Currently I get paper request which has general info such as date , time , book however the discipline is specific. I have one work book with different tabs for the disciplines
26020

Is there any way that a form may be created and based on the discipline selected , the info is saved into that specific tab.

Just trying to minimize time and paper
many thanks

[SamT]

Multipage Control: One page per Discipline, "Discipline" as the Page Caption, each Page gets its own set of I/O controls

"Regular" Form, with a Discipline selecting ListBox Control which sets the Module level Sheet Object Variable the data will be saved to.

[mml]

Thanks for the reply . You wouldn't have an example would you ?

[Paul_Hossler]

IF the fields are the same between disciplines, then I'd use a TabStrip control. IF NOT, then the logic can be adapted to a MultiPage control


On CROWN & BRIDGE, the button runs the demo macro

This is the userform code:




Option Explicit


Private Sub cbClose_Click()
Me.Hide
Unload Me
End Sub


Private Sub cbSave_Click()
Dim wsToUpdate As Worksheet
Dim rToUpdate As Range

With Me
Select Case .TabStrip1.Value ' 0 to 4
Case 0
Set wsToUpdate = Worksheets("CROWN & BRIDGE")
Case 1
Set wsToUpdate = Worksheets("ENDO")
Case 2
Set wsToUpdate = Worksheets("GDP")
Case 3
Set wsToUpdate = Worksheets("UNABLE TO FILL")
Case 4
Set wsToUpdate = Worksheets("COMPLETE")
End Select

With wsToUpdate
Set rToUpdate = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
End With

rToUpdate.Cells(1, 1).Value = .tbName.Value
rToUpdate.Cells(1, 2).Value = .tbDate.Value
rToUpdate.Cells(1, 3).Value = .tbTitle.Value

'this clears the input
.tbName.Value = vbNullString
.tbDate.Value = vbNullString
.tbTitle.Value = vbNullString
End With


End Sub

[mml]

Ohh my goodness that looks perfect ! Cant thank you enough ( sincerely ) . let me digest the info as I am a newbie . one question is there any restrictions if the spread sheet is shared ?

[Paul_Hossler]

Ohh my goodness that looks perfect ! Cant thank you enough ( sincerely ) . let me digest the info as I am a newbie . one question is there any restrictions if the spread sheet is shared ?


Format is pretty crude and rough - I just wanted to show the idea
If maintainability be comes a concern (e.g. Adding another worksheet, etc.) it can be made more robust, and hence more complex
I tried to use a very straight forward technique, even if it's not the most elegant or bullet-proof

Feel free to ask questions

I'm not aware of any issues with sharing something like this, other than the usual issues with sharing a workbook

[mml]

Even a basic form is a vast improvement to what I am using now ... appreciated . I have found a couple of other examples that I can adjust . Questions will come