Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database in Excel Macros (97+ (Using all upto XP))

    In Excel, if you define a named range to be 'Database', all these nifty hidden features come alive.

    However, when I record a Macro to show how to add records and modify them, despite however much I do whilst using the built in form, all I get from the Macro Recorder is one line...

    ActiveSheet.ShowDataForm

    Has anybody written a Macro (VBA Code) that can replicate the actions of using the dataform. Specifically I want to add new records to the Database whilst in a macro, and that means duplicating formats and formulas the way the DataForm does.

    Cheers

    James

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    If you are using a macro, why not add them directly to the database itself. No need to worry about the DataForm. This is only the user interface. After you add the entries you can just copy the format from the row above the start to all the rows you add.


    Steve

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    Hi Steve,

    Just to clarify things, What you are suggesting is exactly what I am trying to do!!!!

    I have no intention of using the builtin dataform.

    The problem is that when you add new records(rows) to the database using the dataform, it automatically extends the Database named range (which is easy to do in VBA) but it also copies any field (cell) formulas and formating.

    I simple copy and paste copies everything including values. I just want formulas and formatting etc. IE a blank record!!!

    This is what I have been able to achieve since I posted...

    <code>
    Dim myCell As Range
    ' Select the first Record in the Database
    ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1).Select
    ' Insert a new line just above it. This automatically redefines the named range as well.
    Selection.Insert Shift:=xlDown
    ' But the calculated fields (formulas) and formats (sometimes conditional) haven't been copied...
    ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1).Select
    For Each myCell In Selection
    'Set myCell.FormatConditions = myCell.Offset(1, 0).FormatConditions '<<< Doesn't work
    If myCell.Offset(1, 0).HasFormula Then
    myCell.FormulaR1C1 = myCell.Offset(1, 0).FormulaR1C1
    End If
    Next myCell

    </code>

    There are the individual Formats and Conditional Formats as well.

    I need ideas....

    Cheers

    James

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    As far as I know you should be able to simply copy the cells with formulas and any formatting AND conditional formatting goes with it. Change thisline:

    myCell.FormulaR1C1 = myCell.Offset(1, 0).FormulaR1C1

    to

    myCell.Copy Destination:= myCell.Offset(1, 0)

    (untested)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    I assume that "database" includes the "header row" at the top and that the header row is not formatted like the rest of the table.
    You are insering a new record right after the header row, excel copies the format in the insert from the row ABOVE (which is the header row)

    To use your code as is, you could:
    Format the header row like the rest of the database
    change the named range to NOT include the header (it will put the new record as record 2 instead of 1
    Change the 2 offset commands from(1,0) to (2,0) and this will insert a new 2nd record under the first, but will have the formatting automatically from the first record

    Or there are other options:
    Modify the code to Copy the format from row 2 to row 1 [copy paste-special formats and validation (as appropriate)]
    Other similar variants

    I don't know if there is a reason to enter a new record at the top (Usually I would put them at the bottom

    Hope this helps,
    Steve

  6. #6
    New Lounger
    Join Date
    May 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    Thanks for the tip boss. Inserting after the 1st record (ie before the 2nd) does most of the trick automatically, but doesn't take into account an empty Database.

    I haven't done the validation thing as I am not using any.

    Here is the code I am using and it works

    <pre>



    Dim myCell As Range

    Application.ScreenUpdating = False
    ' Select the 1st Record.
    ' NB: There will always be at least 1 record even in an empty Database
    ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1) _
    .Insert Shift:=xlDown
    ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1) _
    .Select
    ' Now copy the formats and formulas
    For Each myCell In Selection
    ' Get the formats
    myCell.Offset(1, 0).Copy
    myCell.PasteSpecial Paste:=xlPasteFormats
    ' Check to see if it is a data field or a calculated field
    If myCell.Offset(1, 0).HasFormula Then
    ' Copy the formula
    myCell.FormulaR1C1 = myCell.Offset(1, 0).FormulaR1C1
    End If
    ' Do a sneaky that even the form doesn't do and pre-seed some fields
    Select Case myCell.Offset(-1, 0).Value
    Case "Field3", "Field75", "Field203"
    myCell.Value = False
    Case "Field35", "Field63"
    myCell.Value = Now()
    Case Else
    End Select
    Next myCell
    Application.CutCopyMode = False ' Turn off from previous copy
    Cells(1, 1).Select 'Cell A1
    Application.ScreenUpdating = True

    </pre>




    Cheers

    James

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    Couple suggestions:

    <hr>ThisWorkbook.Names("Database").RefersToRange.O ffset(1, 0).Resize(1) _
    .Insert Shift:=xlDown
    ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1) _
    .Select ' Now copy the formats and formulas

    For Each myCell In Selection<hr>

    would be "better"
    <pre>dim rng as range

    set rng = ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1)

    with rng
    .Insert Shift:=xlDown
    .Select ' Now copy the formats and formulas
    end with
    For Each myCell In rng</pre>



    it "cleans" up the code, it doesn't "select" anything, it should run a little faster and if you need to change the reference only 1 place to change it.

    Steve

  8. #8
    New Lounger
    Join Date
    May 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    Hi Steve,

    Ooops.. I think you missed something! The trick here is that between those two lines the definition of "Database" has changed...

    My first line grabs the 1st record and inserts a new record above it.
    The second line then grabs the NEW 1st record.

    Your code would end up copying the old 2nd record (now 3rd) onto the old 1st (now 2nd) and the new 1st wouldn't be constructed properly.

    Thanks for the input tho...



    James

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Database in Excel Macros (97+ (Using all upto XP))

    OOPS <img src=/S/blush.gif border=0 alt=blush width=15 height=15>, You are correct. That is what I get for not testing my response!

    Try this:

    <pre> Dim rng As Range

    Set rng = ThisWorkbook.Names("Database").RefersToRange.Offse t(1, 0).Resize(1)
    rng.Insert Shift:=xlDown
    Set rng = rng.Offset(-1, 0)

    For Each mycell In rng</pre>


    It has the same advantages of my other (though should be correct)!

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •