+ Reply to Thread
Results 1 to 5 of 5

Dyn Named Range update from userform

  1. #1
    KD
    Guest

    Dyn Named Range update from userform

    Hi All:

    Some help here would be great. I am falling a little short on the
    nuances of procedures that include dynamic named range.

    I have a fairly large multipage userform that validates entries,
    calculates some risk metrics and puts all data (input and output) into
    an output worksheet. The output worksheet has a combobox
    (cbodynEditEntry) that allows the user to edit a particular row.
    cbodynEditEntry has a listfillrange that is a dynamic named range
    representing the first column (rangename=dynProjectTitle).

    The problem is not in running a new project through the userform, but
    in editing an existing. My logic was to, upon selection of existing
    project (either in userform or output sheet), find row with same
    project title, delete that row, and repopulate with the modified data
    stored in the userform. When I do this, the first column does not
    delete and update. It only deletes. This then messes up
    cbodynEditEntry. Some sample code follows. Thanks much and hope to
    return the favor.

    ------------------------------------------------------------------------------------------------------------------
    Private Sub Run_Click
    Dim title as Range
    Application.ScreenUpdating = False
    On Error Resume Next

    '''Find matching project title in row and delete row
    Sheets("OutputData").Select
    Set title = Range("dynProjectTitle")._
    Find(cboProjectTitle.Value)
    title.Select
    ActiveCell.EntireRow.Delete shift:=xlShiftUp

    '''Populate from userform ***The A procedure does not work. B works
    fine.***
    Worksheets("OutputData").Range("A65536")._
    End(xlUp)(2).Value = cboProjectTitle.Text
    Worksheets("OutputData").Range("B65536")._
    End(xlUp)(2).Value = cboStateInput.Text

    '''Update dynamic named range in column A named dynProjectTitle
    Sheets("OutputData").cboDynEditEntry._
    ListFillRange = "dynProjectTitle"

    Unload Me
    Application.ScreenUpdating = True

    End Sub


    Thanks again.
    James KD


  2. #2
    Tom Ogilvy
    Guest

    Re: Dyn Named Range update from userform

    try

    '''Update dynamic named range in column A named dynProjectTitle
    Sheets("OutputData").Calculate
    doevents
    Sheets("OutputData").cboDynEditEntry._
    ListFillRange = "dynProjectTitle"


    if that doesn't help, show the formula you are using to define you dynamic
    name range.

    --
    Regards
    Tom Ogilvy


    "KD" <[email protected]> wrote in message
    news:[email protected]...
    > Hi All:
    >
    > Some help here would be great. I am falling a little short on the
    > nuances of procedures that include dynamic named range.
    >
    > I have a fairly large multipage userform that validates entries,
    > calculates some risk metrics and puts all data (input and output) into
    > an output worksheet. The output worksheet has a combobox
    > (cbodynEditEntry) that allows the user to edit a particular row.
    > cbodynEditEntry has a listfillrange that is a dynamic named range
    > representing the first column (rangename=dynProjectTitle).
    >
    > The problem is not in running a new project through the userform, but
    > in editing an existing. My logic was to, upon selection of existing
    > project (either in userform or output sheet), find row with same
    > project title, delete that row, and repopulate with the modified data
    > stored in the userform. When I do this, the first column does not
    > delete and update. It only deletes. This then messes up
    > cbodynEditEntry. Some sample code follows. Thanks much and hope to
    > return the favor.
    >
    > --------------------------------------------------------------------------

    ----------------------------------------
    > Private Sub Run_Click
    > Dim title as Range
    > Application.ScreenUpdating = False
    > On Error Resume Next
    >
    > '''Find matching project title in row and delete row
    > Sheets("OutputData").Select
    > Set title = Range("dynProjectTitle")._
    > Find(cboProjectTitle.Value)
    > title.Select
    > ActiveCell.EntireRow.Delete shift:=xlShiftUp
    >
    > '''Populate from userform ***The A procedure does not work. B works
    > fine.***
    > Worksheets("OutputData").Range("A65536")._
    > End(xlUp)(2).Value = cboProjectTitle.Text
    > Worksheets("OutputData").Range("B65536")._
    > End(xlUp)(2).Value = cboStateInput.Text
    >
    > '''Update dynamic named range in column A named dynProjectTitle
    > Sheets("OutputData").cboDynEditEntry._
    > ListFillRange = "dynProjectTitle"
    >
    > Unload Me
    > Application.ScreenUpdating = True
    >
    > End Sub
    >
    >
    > Thanks again.
    > James KD
    >




  3. #3
    KD
    Guest

    Re: Dyn Named Range update from userform

    Thanks Tom.

    It works, but can you explain to me how it worked? I am familiar with
    'doevents', but not in this usage.

    Thanks again,

    James KD


  4. #4
    Tom Ogilvy
    Guest

    Re: Dyn Named Range update from userform

    The processor works on one thing at a time. within the grand scheme of
    things, the operating system allocates out use of the processor to all the
    different processes and with a process like Excel, VBA also gets allocated
    some processing time. When VBA is running a demanding task, it may not
    relinquish the processor to allow other processes and subprocesses to run.
    Doevents tells VBA to "rest" and let the other processes catch up on their
    work. It isn't a major pause or anything, but sometimes it helps when you
    are depending on other things to happen such as in your case.

    You might try it without the Doevents and see if the calculate alone is
    enough. Or try a ThisWorkbook.Calculate instead of the sheet level
    calculate. Or just leave the Doevents in.

    --
    Regards,
    Tom Ogilvy





    "KD" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks Tom.
    >
    > It works, but can you explain to me how it worked? I am familiar with
    > 'doevents', but not in this usage.
    >
    > Thanks again,
    >
    > James KD
    >




  5. #5
    KD
    Guest

    Re: Dyn Named Range update from userform

    Great. Thank you.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1