VBA Code to Count Number of Filled Spaces in Entry Form

[UncleBajubjubs]

Hello, my workbook is set up to copy a sheet from another workbook into it. Someone will fill out a worksheet with their data, and someone else will use my program to work with the data. The form they fill in has a few spaces for "additional notes", ie, essentially any information that doesn't have a spot to go in.

The program pulls in the data just fine, and I'd like it to have a message box pop up when they use the macro that copies the sheet. The message box will tell them "X number of additional notes were added, please check.", with X being the number of cells designated for "additional notes" that were typed into. Any ideas as to how to do this neatly? Thanks!

 

[Roderick_E]

We'd need to know how you're copying the data to the worksheet, or you could always do a test to see if the "additional notes" cells are blank after the copy, and if not consider that note were added and usg your msgbox to inform user.

 

[UncleBajubjubs]

Basically in my workbook, it opens the dialogue to select a file to link, then it copies the completed form you select to a blank version of that form on a sheet in my workbook. At present, what I have is

<code>
Public Sub Comments()
Dim notes As Integer
notes = 0


Worksheets("the copied worksheet").Activate


If Not IsEmpty("cell1") Then
notes = notes + 1
End If


If Not IsEmpty("cell2") Then
notes = notes + 1
End If


If Not IsEmpty("cell3") Then
notes = notes + 1
End If


If Not IsEmpty("cell4") Then
notes = notes + 1
End If


If Not IsEmpty("cell5") Then
notes = notes + 1
End If

MsgBox notes & " flags were found."


End Sub

</code>

But it's say making notes equal to five even with nothing in those 5 cells. I'm not sure what to do.

 

[Roderick_E]

Basically in my workbook, it opens the dialogue to select a file to link, then it copies the completed form you select to a blank version of that form on a sheet in my workbook. At present, what I have is

<code>
Public Sub Comments()
Dim notes As Integer
notes = 0


Worksheets("the copied worksheet").Activate


If Not IsEmpty("cell1") Then
notes = notes + 1
End If


If Not IsEmpty("cell2") Then
notes = notes + 1
End If


If Not IsEmpty("cell3") Then
notes = notes + 1
End If


If Not IsEmpty("cell4") Then
notes = notes + 1
End If


If Not IsEmpty("cell5") Then
notes = notes + 1
End If

MsgBox notes & " flags were found."


End Sub

</code>

But it's say making notes equal to five even with nothing in those 5 cells. I'm not sure what to do.

How about replacing If Not IsEmpty("cell5") Then with if trim(range("A2"))="" then
Changing A2 to whatever cell it actually is.

 

[UncleBajubjubs]

How about replacing If Not IsEmpty("cell5") Then with if trim(range("A2"))="" then
Changing A2 to whatever cell it actually is.

Thanks, I tried this, however it's still telling me the 5 have data.

 

[Fluff]

Hia
How about

    Dim Ans As Integer
    
    Ans = WorksheetFunction.CountIf(Range("B11:B16"), "<>")
    MsgBox Ans

Changing the range to suit

 

[tonyyy]

Unclebajubjubs,

Welcome to the Board.

We'd need to know how you're copying the data to the worksheet...

@Roderick_E asks a valid question. If your code in post #3 is saying there are 5 notes even when the 5 cells are empty, maybe the copy routine is bringing over formulas or other hidden "data".

Cheers,

tonyyy

 

[UncleBajubjubs]

Thanks for the replies everyone, I was able to get it working!