[Solved] Macro to check for blank cells

[cmeylaq]

Hi guys...need your assistance...below and attached is a macro to return "ALL Good" if cells B1 B2 and B3 IS NOT BLANK.
If any of these are BLANK then it should return the respective message.
The below is always returning "ALL GOOD" even when the cells are blank.

Any help is much appreciated.

Code: Select all

Sub checkforblanks
sheet = ThisComponent.CurrentController.ActiveSheet
TimeFrom = sheet.getCellRangeByName("B1").value
TimeTo = sheet.getCellRangeByName("B2").value
AttName = sheet.getCellRangeByName("B3").value

If Timefrom = "" then 
Msgbox "Update Time From" 
else
If Timeto = "" then 
Msgbox "Update Time To" 
else
If AttName = "" then 
Msgbox "Update Attendant Name" 
else msgbox  "All Good" 
Endif
Endif
Endif

End Sub

[JeJe]

This is exactly the same question as your last post. change .value to .string.

[cmeylaq]

Thank you JeJe. Apologies

[MrProgrammer]

Learn how to use breakpoints and the Watch window so you can discover for yourself that the value of an empty cell is zero. Or search the forum for information on how to use object inspectors like MRI and Xray. Attempting to write macros without these tools is effectively impossible. A SELECT statement (instead of IF) will simplify your macro,

202304241201.gif (103.87 KiB) Viewed 1407 times

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Lupp]

The question explicitly asked how to check cells for being BLANK. Neither a cell having the .Value 0 nor one with empty .String can be assured to be BLANK. A BLANK cell may be formatted, but must not contain anything. This includes that a cell containing a formula returning the result "" (empty string) is not BLANK. The standard function ISBLANK() asked for the state of such a cell will return FALSE.

A macro needing to check a cell object, say oCell, for being BLANK has to use the comparison oCell.Type=0

[cmeylaq]

thank you all for your input. much appreciated.