Hi All,

I am hoping one of you guys can help.

I have a userform with multiple textboxes etc that i want to transfer
to a workbook containing numerous sheets. I can do it if it is to one
sheet but some of the information is dependant on the outcome of
checkboxes etc. In addition to this i am using checkboxes to determine
which worksheets get the information loaded.

I did think that if i set the sheet name used in the main procedure as
a variable and have this set from the state of the checkboxes, but i
just cant seem to get the variables right and get errors.

Any help would be greatly appreciated!

Andy

Andy

From what I underatand you want to update different worksheets based on the
values of the user dialog.

One way to do it is when the usrr clicks the 'ok' or 'do it' button that you
check the status of the check boxes and update the variable accordingly, you
then use the variable to update the data as in:
'---------
dim sz_sheetname as string
sz_sheetname = "the name of the sheet"
activeworkbook.worksheets(sz_sheetname).range("A1" ) = "what ever!"
'----------
You then have to decide on how to maintain the list of links and I prefer to
have a datasheet in the macro book where all the data is and I would use the
following.
'----------
Private Sub CommandButton1_Click()
Dim sz_worksheet_to_use As String
If IsNull(Me.CheckBox1) Then Me.CheckBox1.Value = False
If Me.CheckBox1.Value = True Then
ThisWorkbook.Worksheets("Data").Range("B2") = "SheetCheck1"
Else
ThisWorkbook.Worksheets("Data").Range("B2") = "SheetNotCheck1"
End If
sz_worksheet_to_use = ThisWorkbook.Worksheets("Data").Range("B2")
ActiveWorkbook.Worksheets(sz_worksheet_to_use).Ran ge("A1") = "Some value"
thisworkbook.Save ' save the updated reference
End Sub

'----------
Here I have a workbook with two worksheets in it called SheetCheck1 and
SheetNotCheck1 and the data sheet in the macrobook.

With this approach I can load the default values when I load the form with:

Private Sub UserForm_Initialize()
If ThisWorkbook.Worksheets("Data").Range("B2") = "SheetCheck1" Then
Me.CheckBox1.Value = True
ElseIf ThisWorkbook.Worksheets("Data").Range("B2") = "SheetNotCheck1" Then
Me.CheckBox1.Value = False
End If
End Sub

Note that activeworkbook is the workbook where you called the macro from and
thisworkbook is the workbook where the macro and form is save in.

Hope this is clear.
--
HTHs Martin


"andymcgooner" wrote:

Hi All,

I am hoping one of you guys can help.

I have a userform with multiple textboxes etc that i want to transfer
to a workbook containing numerous sheets. I can do it if it is to one
sheet but some of the information is dependant on the outcome of
checkboxes etc. In addition to this i am using checkboxes to determine
which worksheets get the information loaded.

I did think that if i set the sheet name used in the main procedure as
a variable and have this set from the state of the checkboxes, but i
just cant seem to get the variables right and get errors.

Any help would be greatly appreciated!

Andy

Hi Martin,
I appreciate the response and the help, but in all honesty i am
relatively new to VB and maybe i bit off a little more than i can chew
but intent on completing this project.

Anyway i understand the principal off what you are saying but were you
are coming from but dont think i fully explained what i am trying to
do. I have a user form that opens when the workbook is started that
allows a user to input data into some text boxes and select some
options. However thier are instances when the data has to go to more
than one worksheet say sheet1, sheet2 & sheet3. As i said i have
already done it so that it will load the data to one sheet but it is
getting it to do it to multiple ones. i have a scaled down version
which i have been using to test some methods on so that i dont screw up
the main one, however code is more or less the same (see below). As i
said i appreciate any assistance and dont know if i am maybe
interpreting your code wrong but i cant seem to bolt it together.

Sub updateworksheetstest()
Sheets("Sheet1").Activate
MyDate = Date
If IsEmpty(Range("A2")) Then
Row = ActiveCell.Row + 1
Else
ActiveCell.Offset(1, 0).EntireRow.Insert
Range("A1").End(xlDown).Select
Row = ActiveCell.Row + 1
End If
'UserForm1.TextBox1.Text = MyDate
Cells(Row, 1).Select
Cells(Row, 1) = UserForm1.TextBox1.Text
Cells(Row, 2) = UserForm1.TextBox2.Text
Cells(Row, 3) = UserForm1.TextBox3.Text
UserForm1.Hide
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
UserForm1.TextBox2.Text = ""
UserForm1.Show
End Sub

Martin Fishlock wrote:
Andy

From what I underatand you want to update different worksheets based on the
values of the user dialog.

One way to do it is when the usrr clicks the 'ok' or 'do it' button that you
check the status of the check boxes and update the variable accordingly, you
then use the variable to update the data as in:
'---------
dim sz_sheetname as string
sz_sheetname = "the name of the sheet"
activeworkbook.worksheets(sz_sheetname).range("A1" ) = "what ever!"
'----------
You then have to decide on how to maintain the list of links and I prefer to
have a datasheet in the macro book where all the data is and I would use the
following.
'----------
Private Sub CommandButton1_Click()
Dim sz_worksheet_to_use As String
If IsNull(Me.CheckBox1) Then Me.CheckBox1.Value = False
If Me.CheckBox1.Value = True Then
ThisWorkbook.Worksheets("Data").Range("B2") = "SheetCheck1"
Else
ThisWorkbook.Worksheets("Data").Range("B2") = "SheetNotCheck1"
End If
sz_worksheet_to_use = ThisWorkbook.Worksheets("Data").Range("B2")
ActiveWorkbook.Worksheets(sz_worksheet_to_use).Ran ge("A1") = "Some value"
thisworkbook.Save ' save the updated reference
End Sub

'----------
Here I have a workbook with two worksheets in it called SheetCheck1 and
SheetNotCheck1 and the data sheet in the macrobook.

With this approach I can load the default values when I load the form with:

Private Sub UserForm_Initialize()
If ThisWorkbook.Worksheets("Data").Range("B2") = "SheetCheck1" Then
Me.CheckBox1.Value = True
ElseIf ThisWorkbook.Worksheets("Data").Range("B2") = "SheetNotCheck1" Then
Me.CheckBox1.Value = False
End If
End Sub

Note that activeworkbook is the workbook where you called the macro from and
thisworkbook is the workbook where the macro and form is save in.

Hope this is clear.
--
HTHs Martin


"andymcgooner" wrote:

Hi All,

I am hoping one of you guys can help.

I have a userform with multiple textboxes etc that i want to transfer
to a workbook containing numerous sheets. I can do it if it is to one
sheet but some of the information is dependant on the outcome of
checkboxes etc. In addition to this i am using checkboxes to determine
which worksheets get the information loaded.

I did think that if i set the sheet name used in the main procedure as
a variable and have this set from the state of the checkboxes, but i
just cant seem to get the variables right and get errors.

Any help would be greatly appreciated!

Andy

Andy,

To put data on another worksheet say sheet2 in the same (active) workbook,
you would use this:

activeworkbook.worksheets("sheet2").range("A1") = "what ever!"

as you see sheet2 and A1 are strings so you could write:

if UserForm1.checkbox1 = true then
a_cell="A1"
a_sheet ="sheet2"
else
a_cell="A3"
a_sheet ="sheet3"
endif
activeworkbook.worksheets(a_sheet).range(a_cell) = "what ever!"

I find it easier to use a variable for the active sheet which is the major
one so you could write instead of Sheets("Sheet1").Activate

dim ws as worksheet
set ws = Sheets("Sheet1")


--
Hope this helps
Martin Fishlock


"andymcgooner" wrote:

Hi Martin,
I appreciate the response and the help, but in all honesty i am
relatively new to VB and maybe i bit off a little more than i can chew
but intent on completing this project.

Anyway i understand the principal off what you are saying but were you
are coming from but dont think i fully explained what i am trying to
do. I have a user form that opens when the workbook is started that
allows a user to input data into some text boxes and select some
options. However thier are instances when the data has to go to more
than one worksheet say sheet1, sheet2 & sheet3. As i said i have
already done it so that it will load the data to one sheet but it is
getting it to do it to multiple ones. i have a scaled down version
which i have been using to test some methods on so that i dont screw up
the main one, however code is more or less the same (see below). As i
said i appreciate any assistance and dont know if i am maybe
interpreting your code wrong but i cant seem to bolt it together.

Sub updateworksheetstest()
Sheets("Sheet1").Activate
MyDate = Date
If IsEmpty(Range("A2")) Then
Row = ActiveCell.Row + 1
Else
ActiveCell.Offset(1, 0).EntireRow.Insert
Range("A1").End(xlDown).Select
Row = ActiveCell.Row + 1
End If
'UserForm1.TextBox1.Text = MyDate
Cells(Row, 1).Select
Cells(Row, 1) = UserForm1.TextBox1.Text
Cells(Row, 2) = UserForm1.TextBox2.Text
Cells(Row, 3) = UserForm1.TextBox3.Text
UserForm1.Hide
UserForm1.TextBox1.Text = ""
UserForm1.TextBox2.Text = ""
UserForm1.TextBox2.Text = ""
UserForm1.Show
End Sub

Martin Fishlock wrote:
Andy

From what I underatand you want to update different worksheets based on the
values of the user dialog.

One way to do it is when the usrr clicks the 'ok' or 'do it' button that you
check the status of the check boxes and update the variable accordingly, you
then use the variable to update the data as in:
'---------
dim sz_sheetname as string
sz_sheetname = "the name of the sheet"
activeworkbook.worksheets(sz_sheetname).range("A1" ) = "what ever!"
'----------
You then have to decide on how to maintain the list of links and I prefer to
have a datasheet in the macro book where all the data is and I would use the
following.
'----------
Private Sub CommandButton1_Click()
Dim sz_worksheet_to_use As String
If IsNull(Me.CheckBox1) Then Me.CheckBox1.Value = False
If Me.CheckBox1.Value = True Then
ThisWorkbook.Worksheets("Data").Range("B2") = "SheetCheck1"
Else
ThisWorkbook.Worksheets("Data").Range("B2") = "SheetNotCheck1"
End If
sz_worksheet_to_use = ThisWorkbook.Worksheets("Data").Range("B2")
ActiveWorkbook.Worksheets(sz_worksheet_to_use).Ran ge("A1") = "Some value"
thisworkbook.Save ' save the updated reference
End Sub

'----------
Here I have a workbook with two worksheets in it called SheetCheck1 and
SheetNotCheck1 and the data sheet in the macrobook.

With this approach I can load the default values when I load the form with:

Private Sub UserForm_Initialize()
If ThisWorkbook.Worksheets("Data").Range("B2") = "SheetCheck1" Then
Me.CheckBox1.Value = True
ElseIf ThisWorkbook.Worksheets("Data").Range("B2") = "SheetNotCheck1" Then
Me.CheckBox1.Value = False
End If
End Sub

Note that activeworkbook is the workbook where you called the macro from and
thisworkbook is the workbook where the macro and form is save in.

Hope this is clear.
--
HTHs Martin


"andymcgooner" wrote:

Hi All,

I am hoping one of you guys can help.

I have a userform with multiple textboxes etc that i want to transfer
to a workbook containing numerous sheets. I can do it if it is to one
sheet but some of the information is dependant on the outcome of
checkboxes etc. In addition to this i am using checkboxes to determine
which worksheets get the information loaded.

I did think that if i set the sheet name used in the main procedure as
a variable and have this set from the state of the checkboxes, but i
just cant seem to get the variables right and get errors.

Any help would be greatly appreciated!

Andy