first off guys id like to say hello! iv been stopping by the forums for a few months now gathering my little nuggets of information! And it is much appreciated!

basically my problem is i have created a large fully working rostering form with counters, formatting and all sorts on the main form, this all feeds back to a calculation sheet to calculate spare turns (turns that are averaged from remaining hours ect.)
so my colleague loves the new form and has taken it to our boss who now wants me to roll it out to other rostering clerks!!! EEEP! i think! as he wants me to redesign it so that a user can input a number into the starting form (refering to the number of lines/people he wants to be generated, as all cleakrs have different and fluctuating members of staff and therefor lines on the roster)

now i know my way around excel but have never touched apon macros and buttons, i need it so that a user can pop a number in a cell then click a button. then the button either pulls a default line and replicates for the number in the box or just generates it into a new sheet.

1. is this possible
2.could you point me in the right direction for info relating to this or maybe a demo form with a button similar to this that i can break down and work with.

once again guys thanks for all the help you guys give and if you could help me id be very appreciative!

sorry if its a bit confusing. if ya need any more info let me know.

regards
chris

The method I use to insert rows (or columns) into a pre-defined table
is to use a blank row/col from the table, hide these to the left/top of
the table, and use a menuitem in the right-click popup menu. The
choices are...

Insert Rows
Above here
Below here
Add more rows
------------------
Insert Columns
Before he
After he
Add more columns

...where 'Insert Rows' and 'Insert Columns' are 'popup' menus with their
respective submenus under them. Users are prompted to enter the
quantity to insert.

The hidden table row/col are assigned defined names with local (sheet
level) scope as follows:

Name :: RefersTo
****************
"'Sheet1'!BlankRow" :: "='Sheet1'!$1:$1"
"'Sheet1'!BlankCol" :: "='Sheet1'!$A:$A"

The code uses Workbook_SheetBeforeRightClick event. It checks if the
'Target' cell is located within the table and if so, it prompts for a
quantity and insert those rows/cols based on the value stored in the
menu's 'Tag' and 'Parameter' properties as follows:

Insert Rows
Above he Tag="above"; Parameter="row"
Below he Tag="below"; Parameter="row"
Add more rows: Tag="add"; Parameter="row"
------------------
Insert Columns
Before he Tag="before"; Parameter="col"
After he Tag="after"; Parameter="col"
Add more columns: Tag="add"; Parameter="col"

If the Tag is "below" or "after" it sets a ref to the row below (or col
after) the active cell so the 'shift:' arg is always the same (xldown,
xlToRight).

Example:

Sub AddColsRows()
Dim lPos As Long, vCount As Variant, vCalc As Variant

vCount = InputBox("How many?", Default:=1)
If vCount = "" Then Exit Sub '//user cancels

'Suppress screen activity and events
With Application
.ScreenUpdating = False: .EnableEvents = False
vCalc = .Calculation: .Calculation = xlCalculationManual
End With

'Get the criteria
With Commandbars.ActionControl
If .Parameter = "row" Then lPos = ActiveCell.Row _
Else lPos = ActiveCell.Column
Select Case .Tag
Case "below", "after": lPos + 1
Case "add"
If .Parameter = "row" Then lPos = Range("InputEndRow").Row _
Else lPos = Range("InputEndCol").Column
End Select
'**Note that 'InputEnd???' refs a narrow border row/col located
'below and right of the table's input area.

'Insert blank ranges
If .Parameter = "row" Then
With Range("BlankRow")
.EntireRow.Hidden = False: .Copy
Rows(lPos).Resize(vCount).Insert Shift:=xlDown
.EntireRow.Hidden = True
End With
Else
With Range("BlankCol")
.EntireColumn.Hidden = False: .Copy
Columns(lPos).Resize(vCount).Insert Shift:=xlToRight
.EntireColumn.Hidden = True
End With
End If
End With

'Restore screen activity and events
With Application
.CutCopyMode = False: .Calculation = vCalc
.ScreenUpdating = True: .EnableEvents = True
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry thanks for that. idealy though i would like it so that you have one sheet to start lets call it calc. then when the user defines the parameters of the number of lines for the roster (thats all they will need to do as columns are pre defined) and clicks the button, excel creates a new sheet, names it and inputs the Number of lines the user has defined (along with the columns (i think i have about 10-15 columns)). would this be possible? i presume i would have to pull the columns and relevant default data and calculations from another sheet/workbook?

do you think that could work? im just having trouble on getting it started.

regards
chris
p.s sorry to be a pain :D

omen666blue has brought this to us :
Hi Garry thanks for that. idealy though i would like it so that you have
one sheet to start lets call it calc. then when the user defines the
parameters of the number of lines for the roster (thats all they will
need to do as columns are pre defined) and clicks the button, excel
creates a new sheet, names it and inputs the Number of lines the user
has defined (along with the columns (i think i have about 10-15
columns)). would this be possible? i presume i would have to pull the
columns and relevant default data and calculations from another
sheet/workbook?

do you think that could work? im just having trouble on getting it
started.

My suggestion obviates the need to 'pull' data from other sheets. I
think it would be more efficient to have everyone populate a single
sheet, and use AutoFilter to view individual group entries. You could
also use the 'Group and Outline' feature so you can expand/collapse the
groups on a single sheet. This way, groups can expand their own
sections to input data.

Your blank rows/columns should be configured the same way. In fact, you
can insert entire group sections so this happens 'on-the-fly' as
needed. Each group can also contain its own summary area since the
'BlankGroup' would also be a local defined name, and 'BlankRow' would
then be a single row within 'BlankGroup'.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

ok well i will give this a try, i can gut the main bulk of using a single sheet for many rosters, as the sheets will be hosted locally for each clerk. ill get working on this and post any problems i come across! Im sure there will be some as im very new when it comes to the more advanced aspects :D

thanks for your help Garry!

omen666blue expressed precisely :
ok well i will give this a try, i can gut the main bulk of using a
single sheet for many rosters, as the sheets will be hosted locally for
each clerk. ill get working on this and post any problems i come across!
Im sure there will be some as im very new when it comes to the more
advanced aspects :D

thanks for your help Garry!

Sorry for the deflection but I'd rather steer you onto a better path
for going forward than help you with a problematic one now. If you can
send me your file via attachment or link to an unpload site I'll take a
look at it. Please be sure it fully explains/examples what you're
trying to do!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion