Use a macro to create new sheets
 

I have a workbook with two sheets. The first sheet has a list of about
20 items and a dialog box adjacent to them that enables the user to
select the item. The second sheet is a basic form template.

What I want to do is create a macro that looks at which dialog boxes
are selected and then creates copies of the second sheet for each item
selected and have the sheets named according to the item name.

Anyone know if this is possible or how to accomplish this? Any help
would be greatly appreciated!

Use a macro to create new sheets
 

Very possible -

Turn on the macro recorder and do it all. Than go into theVBE and edit the
code generated.

--
steveB

Remove "AYN" from email to respond
wrote in message
ups.com...
I have a workbook with two sheets. The first sheet has a list of about
20 items and a dialog box adjacent to them that enables the user to
select the item. The second sheet is a basic form template.

What I want to do is create a macro that looks at which dialog boxes
are selected and then creates copies of the second sheet for each item
selected and have the sheets named according to the item name.

Anyone know if this is possible or how to accomplish this? Any help
would be greatly appreciated!

Use a macro to create new sheets
 

Thank you for the reply Steve. I guess I should have said that I am
new to the use of macros. I have always done everything I could to
complete tasks with a combination of functions and have never used
macros before.

So, I have decided to switch to checkboxes because it appears as if you
cannot select multiple radio buttons, but I do not know how to do a
"record macro" in the easy manner you described. I need an update
button to accomplish the task but how do I get the update button to
reference the selected cells and create newly copied sheets?

Thank again,

Johnny

Use a macro to create new sheets
 

Without seeing your workbook, it is hard to give you code or hard advice.

But here are some sites where you can get all kinds of code and helpful
advice:

http://www.cpearson.com/excel.htm

http://www.contextures.com/index.html

http://www.peltiertech.com/index.html

http://j-walk.com/

http://www.appspro.com/

http://www.rondebruin.nl/tips.htm

www.tushar-mehta.com

http://www.xldynamic.com/source/xld.html


Macros will do just about anything you do manually, and than much more.

To record a macro:
Select the Tools menu
Select Macro
Select Record Macro

than just start doing stuff.
When you are finished - repeat the above steps but turn off Record Macro.

Now go to the VBE and see what you got. Edit it as needed.

Understand that the recording will use a lot of Selection or Selection -
most code doesn't need that.
You can work directly with workbooks, worksheets, ranges, cells without
selecting (most of the time).

Buttons usually have a macro assigned to them and when clicked the macro
will run.

Keep using the group to get further info. Monitor some of the messages to
see how people are doing things - there's great stuff here... and the people
are super...

--
steveB

Remove "AYN" from email to respond
wrote in message
oups.com..
Thank you for the reply Steve. I guess I should have said that I am
new to the use of macros. I have always done everything I could to
complete tasks with a combination of functions and have never used
macros before.

So, I have decided to switch to checkboxes because it appears as if you
cannot select multiple radio buttons, but I do not know how to do a
"record macro" in the easy manner you described. I need an update
button to accomplish the task but how do I get the update button to
reference the selected cells and create newly copied sheets?

Thank again,

Johnny

Use a macro to create new sheets
 

P.S.

It helps if your reply contains previous messages, makes it easier to follow
what has been said before...

thanks...

--
steveB

Remove "AYN" from email to respond
"STEVE BELL" wrote in message
news:X0%ye.20445$H64.19763@trnddc07...
Without seeing your workbook, it is hard to give you code or hard advice.

But here are some sites where you can get all kinds of code and helpful
advice:

http://www.cpearson.com/excel.htm

http://www.contextures.com/index.html

http://www.peltiertech.com/index.html

http://j-walk.com/

http://www.appspro.com/

http://www.rondebruin.nl/tips.htm

www.tushar-mehta.com

http://www.xldynamic.com/source/xld.html


Macros will do just about anything you do manually, and than much more.

To record a macro:
Select the Tools menu
Select Macro
Select Record Macro

than just start doing stuff.
When you are finished - repeat the above steps but turn off Record Macro.

Now go to the VBE and see what you got. Edit it as needed.

Understand that the recording will use a lot of Selection or Selection -
most code doesn't need that.
You can work directly with workbooks, worksheets, ranges, cells without
selecting (most of the time).

Buttons usually have a macro assigned to them and when clicked the macro
will run.

Keep using the group to get further info. Monitor some of the messages to
see how people are doing things - there's great stuff here... and the
people are super...

--
steveB

Remove "AYN" from email to respond
wrote in message
oups.com..
Thank you for the reply Steve. I guess I should have said that I am
new to the use of macros. I have always done everything I could to
complete tasks with a combination of functions and have never used
macros before.

So, I have decided to switch to checkboxes because it appears as if you
cannot select multiple radio buttons, but I do not know how to do a
"record macro" in the easy manner you described. I need an update
button to accomplish the task but how do I get the update button to
reference the selected cells and create newly copied sheets?

Thank again,

Johnny

Use a macro to create new sheets
 

Thank you very much for the references. I had already been to a few of
them but most were new to me. Right now I have attempted to get
through the first part with some success but then made changes and
didn't save in time.

Just for clarification, here is what I am trying to do:

I have a list of check boxes on sheet one. Each checkbox is a product
and there are 15 checkbox items but that quantity will expand
periodically. The user will click a few or possibly all of the
checkboxes and then click an update button. The update button will
create a new sheet for each selected box. The new sheet will be a copy
of sheet2 which is a general template that they will fill out. The new
sheets will be named corresponding to the checkbox name and a cell in
the new sheets will also contain the corresponding checkbox name.

The items listed on sheet1 are Carmine, Earlibrite, Festival, Galante,
etc... If selected then these names will also be the names of the
sheets and the header or title on each of those sheets.

The code I currently have is the following:

Private Sub UpdateButton_Click()

Dim ctl As OLEObject

For Each ctl In Sheet2.OLEObjects
If TypeName(ctl.Object) = "CheckBox" Then
If ctl.Object.Value = True Then
Worksheets.Add After:=Worksheets("Sheet2")
Worksheets(Worksheets.Count - 1).Name = CheckBox.Name
CheckBox.Name = ""

End If
End If
Next ctl
End Sub

In the meantime I have ordered John Walkenbach's book on VBA
programming because his book Excel Formulas 2000 has been my bible.

Thank you Steve

Use a macro to create new sheets
 

Working with check boxes on a sheet is not my favorite thing.

so I use a selection-change macro to put an X in column A, or remove it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
If Len(Target) = 0 Then
Target = "X"
Else
Target.ClearContents
End If
End Sub

Than my update code just loops through column A and responds if column A =
"X",
or if len(cel)0 or Ucase(cel)= "X" or .....

see if this helps....
keep me posted...
--
steveB

Remove "AYN" from email to respond
wrote in message
oups.com...
Thank you very much for the references. I had already been to a few of
them but most were new to me. Right now I have attempted to get
through the first part with some success but then made changes and
didn't save in time.

Just for clarification, here is what I am trying to do:

I have a list of check boxes on sheet one. Each checkbox is a product
and there are 15 checkbox items but that quantity will expand
periodically. The user will click a few or possibly all of the
checkboxes and then click an update button. The update button will
create a new sheet for each selected box. The new sheet will be a copy
of sheet2 which is a general template that they will fill out. The new
sheets will be named corresponding to the checkbox name and a cell in
the new sheets will also contain the corresponding checkbox name.

The items listed on sheet1 are Carmine, Earlibrite, Festival, Galante,
etc... If selected then these names will also be the names of the
sheets and the header or title on each of those sheets.

The code I currently have is the following:

Private Sub UpdateButton_Click()

Dim ctl As OLEObject

For Each ctl In Sheet2.OLEObjects
If TypeName(ctl.Object) = "CheckBox" Then
If ctl.Object.Value = True Then
Worksheets.Add After:=Worksheets("Sheet2")
Worksheets(Worksheets.Count - 1).Name = CheckBox.Name
CheckBox.Name = ""

End If
End If
Next ctl
End Sub

In the meantime I have ordered John Walkenbach's book on VBA
programming because his book Excel Formulas 2000 has been my bible.

Thank you Steve