Dyn Named Range update from userform

[KD]

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

 

[Tom Ogilvy]

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.

 

[KD]

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

 

[Tom Ogilvy]

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.

 

[KD]

Great. Thank you.