input field linked to button to create a table

[omen666blue]

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

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

now i know my way around excel but have never touched apon macros an
buttons, i need it so that a user can pop a number in a cell then clic
a button. then the button either pulls a default line and replicates fo
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 o
maybe a demo form with a button similar to this that i can break dow
and work with.

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

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

regards
chri

 

[GS]

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 here:
After here:
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 here: Tag="above"; Parameter="row"
Below here: Tag="below"; Parameter="row"
Add more rows: Tag="add"; Parameter="row"
------------------
Insert Columns
Before here: Tag="before"; Parameter="col"
After here: 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

 

[omen666blue]

;1602831']The method I use to insert rows (or columns) into
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 Row
Above her
Below her
Add more row
-----------------
Insert Column
Before h
After h
Add more column

...where 'Insert Rows' and 'Insert Columns' are 'popup' menus with thei

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 :: RefersT
***************
"'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 Row
Above he Tag="above"; Parameter="row
Below he Tag="below"; Parameter="row
Add more rows: Tag="add"; Parameter="row
-----------------
Insert Column
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 Varian

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

'Suppress screen activity and event
With Applicatio
.ScreenUpdating = False: .EnableEvents = Fals
vCalc = .Calculation: .Calculation = xlCalculationManua
End Wit

'Get the criteri
With Commandbars.ActionContro
If .Parameter = "row" Then lPos = ActiveCell.Row
Else lPos = ActiveCell.Colum
Select Case .Ta
Case "below", "after": lPos +
Case "add
If .Parameter = "row" Then lPos = Range("InputEndRow").Row
Else lPos = Range("InputEndCol").Colum
End Selec
'**Note that 'InputEnd???' refs a narrow border row/col locate
'below and right of the table's input area

'Insert blank range
If .Parameter = "row" The
With Range("BlankRow"
.EntireRow.Hidden = False: .Cop
Rows(lPos).Resize(vCount).Insert Shift:=xlDow
.EntireRow.Hidden = Tru
End Wit
Els
With Range("BlankCol"
.EntireColumn.Hidden = False: .Cop
Columns(lPos).Resize(vCount).Insert Shift:=xlToRigh
.EntireColumn.Hidden = Tru
End Wit
End I
End Wit

'Restore screen activity and event
With Applicatio
.CutCopyMode = False: .Calculation = vCal
.ScreenUpdating = True: .EnableEvents = Tru
End Wit
End Su

--
Garr

Free usenet access at http://www.eternal-september.or
ClassicVB Users Regroup
comp.lang.basic.visual.mis
microsoft.public.vb.general.discussio

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

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

regard
chri
p.s sorry to be a pain :

 

[GS]

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

 

[omen666blue]

ok well i will give this a try, i can gut the main bulk of using
single sheet for many rosters, as the sheets will be hosted locally fo
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 mor
advanced aspects

thanks for your help Garry

 

[GS]

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

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