Forgive me for the length of this post. Thanks for any input.

I am trying to provide as much automation as possible for my users. We are
creating video production assembly sheets that will be printed out and used
in an edit suite to assemble programs for broadcast (as well as eventually
populate a database). The user will start with a template containing a
'header-type' section that states the Title, Program Num., Air date, etc.
There will also be a 'footer-type' section that contains several formulas
pertaining to running time and time left to fill. In-between these sections
I would like to use user-forms that are filled out and then the values sent
to the worksheet in various cells (depending on event type- I have 4 or 5
types on a multi-page form), and formatted appropriately. This will
continue for approximately 20 events (edits, graphics, audio, cutaways,
etc.)

I am accomplishing some of this through macros now, but very limited. The
user-form approach would be ideal for us, because of simplicity and
multiple users. I have created the form and controls and have got a handle
on most events and functions. I am having a problem figuring out how to
identify the cells to the code of the user form. If I go with the insert
rows method, the row and column references are dynamic and I am not able to
hard-code
them. I have considered a Offset method from the current selection but that
becomes clumsy and prone to errors and requires the user to put the cursor
in a specific column or the whole thing goes whacky. I would like to
identify a range of blank cells approx. 9 columns by 3 or 4 rows, then
refer to those cells like A1 as the upper left cell in my range.

As far as naming the range on the worksheet, my thought was to be able to
have a form that loads an edit or event from the sheet to the form for
modification then back to the sheet.
--
Brad Loflin

--
Brad Loflin

Hi Brad,

For example, let's assume you have an edit box for Time on the userform which
needs to go into column D on the sheet.  You could select column D and give
it a name of colTime and you could name the edit box ebTime.  You could then
locate a blank row in the sheet using something like:

 Set oBlank = Range("A1").End(xlDown).Offset(1,0).EntireRow

to give you the first row in which column a is empty (assuming A1 and A2 are
filled).

Your code could then do something like:

 Intersect(oBlank, Range("colTime")).Value = ebTime

Once you get this far, you could either have lots of these rows, or use
something like:

 On Error Resume Next   'Ignore unmatched controls

 For i = 0 To Form1.Control.Count
    Set oCtl = Form1.Controls(i)

    Intersect(oBlank, Range("col" & Mid(oCtl.Name, 3))).Value = oCtl
 Next

 On Error Goto 0

 which will loop through all the controls, putting them in the correct
columns on the destination sheet.  This assumes that each control name has 2
letters to identify the control type, then the remainder of the name is the
part to be used to identify the column where its data is to be stored.

 Note that the code examples above have not been tested!

 Regards

 Stephen Bullen
 Microsoft MVP - Excel
 http://ourworld.compuserve.com/homepages/Stephen_Bullen