How to assign a DCOUNT function to a userform label

[Frank Krogh]

Given the DCOUNT formula DCOUNT(Database;"ColumnName";CriteriaArea)"

How do I assign this formula to a label (lFreq) in a userform
(frmFreqTable)?


Thanks for any suggestions


Frank Krogh

 

[mk]

Given the DCOUNT formula DCOUNT(Database;"ColumnName";CriteriaArea)"

How do I assign this formula to a label (lFreq) in a userform
(frmFreqTable)?

Aaaa, sorry ... but what do you mean here by 'assign'?

 

[Mark Rosenkrantz]

Frank;

I assume you want to display the result of that function in the label.
You can do that by using the caption property of the label.

lFreq.Caption = "x"
where "x" is the result of the function.

Mark.

 

[Guest]

You could set the value of the Label by just running
lFreq.Caption = Application.WorksheetFunction.Dcount(rngList, rngHeader, rngCriteria

Depending on where you put that code, it becomes volatile. For example, if you put that code in the frmFreqTable_Initialize() event subroutine, it's only assigned when the form opens. But if you put it in the Worksheet_Change(Target as Range) event subroutine, the label will update whenever a cell changes. That would make the code execute often though, even when unnecessary; which isn't a big deal if your data range is small. But if it's large enough to make dcount run slowly, you can put something like this in that change event procedure

If Not Intersect(Target, rngList) is Nothing The
lFreq.Caption = Application.WorksheetFunction.Dcount(rngList, rngHeader, rngCriteria
End I

HTH

-Brad Vontur

 

[Frank Krogh]

Yes, but when I do, I just get a run time error 424 "Object required".

Perhaps I need somehow to reference the workbook name and the sheet name
regarding DatabaseRange and CriteriaRange in the formula:
Application.WorksheetFunction.DCOUNT(DatabaseRange,"ColumnName",CriteriaRang
e) ?

Any suggestions?



Frank

 

[Frank Krogh]

OK.

It works fine when I use the frm.myForm.lFreq.caption =
Application.WorksheetFunction.CountBlank(ListRange), but when using the
lFreq.Caption = Application.WorksheetFunction.Dcount(rngList, rngHeader,
rngCriteria), I get a run time error 424 "Object required". What is missing
here?


Frank Krogh

You could set the value of the Label by just running:
lFreq.Caption = Application.WorksheetFunction.Dcount(rngList, rngHeader, rngCriteria)

Depending on where you put that code, it becomes volatile. For example,

if you put that code in the frmFreqTable_Initialize() event subroutine, it's
only assigned when the form opens. But if you put it in the
Worksheet_Change(Target as Range) event subroutine, the label will update
whenever a cell changes. That would make the code execute often though,
even when unnecessary; which isn't a big deal if your data range is small.
But if it's large enough to make dcount run slowly, you can put something
like this in that change event procedure: