Editing an existing macro for a spreadsheet

Hi,

I know almost nothing about VBA, so am having great difficulties with this! I have an existing spreadsheet that has been set up so that you have to fill in data in 3 specific columns before you save the spreadsheet. I want to add a fourth mandatory field. The Visual Basic code I have in the current spreadsheet is as follows:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If Application.CountA(Columns(10)) <> Application.CountA(Columns(20)) Or Application.CountA(Columns(20)) <> Application.CountA(Columns(26)) Then
       Cancel = True
       MsgBox "Please check New Trading Name, Main Usage and Officer's Intials are completed." & vbNewLine & vbNewLine & "This file cannot been saved unless they are."
   End If
End Sub

I thought I could edit it by adding an extra "Or" command like so:-

Application.CountA(Columns(10)) <> Application.CountA(Columns(20)) Or Application.CountA(Columns(20)) <> Application.CountA(Columns(26)) Or Application.CountA(Columns(26)) <> Application.CountA(Columns(27)) Then

However, doing that causes the spreadsheet to refuse to save at all, even if there is data in Column 27. I have entered data for the entire column but it still refuses to believe there's data there. What am I doing wrong? Any help gratefully received. I am using Excel 2003.

Cheers!

Lesley

Hi, and welcome to the forum. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(Since this is your first post I'll change it on this occasion but please note for the futrure)

Almost certainly you have a space or perhaps another non visible character in one of the cells.
Personally for checking things like this I prefer to use formulae in the woorksheet and then check the value of the formula in the macro.

So for instance I'd have a helper column that contained the formula

Formula:

=IF(COUNTA(J1,T1:V1)=4,0,1)

Then a check cell which I'd name say "Check_Complete" which contains the formula

=IF(SUM(check_column)<>0,"Incomplete","")

Then the macro code would be

IF Range("Check_Complete") <> "" Then 
   Cancel = True
   MsgBox "Please check New Trading Name, Main Usage and Officer's Intials are completed." & vbNewLine & vbNewLine & "This file cannot been saved unless they are."
   End
End If