IsBlank() in VBA code?
 

I had the need to use the ISBLANK() worksheet function in some VBA code, so
I went into the editor, typed "WorksheetFunction.Is" and the first thing
that came up in the Intellisense list was IsError. ??? So I scrolled up and
found I had passed items starting with "is," meaing IsError was defintely
the first in the list. I take it this means all worksheet functions are not
necessarily available in VBA macros?

And for reference, in case this was some weird thing that didn't show in
Intellisense but still worked in code (like the Print method of the Printer
object in VB6) I manually typed "IsBlank" and tried to run the macro. I got
an error saying the WorksheetFunction object didn't support this method.

Ultimately, the problem I was trying to solve was one of "Excel being
Excel." I have a spreadsheet that has lots of blank cells (they don't even
contain spaces) which Excel doesn't THINK are blank cells. That is, when I
use the COUNTA() function on a range with these cells, they are counted and
not ignored. (If I simply go into edit mode in the cell and then hit Enter,
Excel wakes up and realizes the cell really is blank.) So what? Well, I have
some macros that do what they do until they encounter a blank cell. I test
for "blankness" by checking the Value of the cell against the empty string.
The problem is that these cells return the empty string for their value but
they're not really blank in Excel's eyes, so I'm looking for a better way to
detect them. If ISBLANK() were available in code then I could check that
instead.

IsBlank() in VBA code?
 

I take it this means all worksheet functions are not
necessarily available in VBA macros?

That's correct. ISBLANK is one not available.

Maybe using Len() as a test will work. It should
return 0 for a blank cell.

HTH,
Merjet

IsBlank() in VBA code?
 

This works to some degree.

Sub Empt()
If IsEmpty(Range("B4")) = True Then
MsgBox "Empty"
Else
MsgBox "Not Empty"
End If
End Sub

"Jeff Johnson" wrote:

I had the need to use the ISBLANK() worksheet function in some VBA code, so
I went into the editor, typed "WorksheetFunction.Is" and the first thing
that came up in the Intellisense list was IsError. ??? So I scrolled up and
found I had passed items starting with "is," meaing IsError was defintely
the first in the list. I take it this means all worksheet functions are not
necessarily available in VBA macros?

And for reference, in case this was some weird thing that didn't show in
Intellisense but still worked in code (like the Print method of the Printer
object in VB6) I manually typed "IsBlank" and tried to run the macro. I got
an error saying the WorksheetFunction object didn't support this method.

Ultimately, the problem I was trying to solve was one of "Excel being
Excel." I have a spreadsheet that has lots of blank cells (they don't even
contain spaces) which Excel doesn't THINK are blank cells. That is, when I
use the COUNTA() function on a range with these cells, they are counted and
not ignored. (If I simply go into edit mode in the cell and then hit Enter,
Excel wakes up and realizes the cell really is blank.) So what? Well, I have
some macros that do what they do until they encounter a blank cell. I test
for "blankness" by checking the Value of the cell against the empty string.
The problem is that these cells return the empty string for their value but
they're not really blank in Excel's eyes, so I'm looking for a better way to
detect them. If ISBLANK() were available in code then I could check that
instead.

IsBlank() in VBA code?
 

Hi Jeff,

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

The ISBLANK function only returns TRUE for a fully empty cell (it returns
FALSE if nothing is displayed as a result of a formula). If that is the
functionality you want in your VBA code, I think this function below will
work for you (just pass in a Range reference for the cell you are
testing)...

Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function

Notice, it is a one-liner.<bg

Rick


"Jeff Johnson" wrote in message
news:_Nednc0WcKVC3ZPVnZ2dnUVZ_h2pnZ2d@datapex...
I had the need to use the ISBLANK() worksheet function in some VBA code, so
I went into the editor, typed "WorksheetFunction.Is" and the first thing
that came up in the Intellisense list was IsError. ??? So I scrolled up and
found I had passed items starting with "is," meaing IsError was defintely
the first in the list. I take it this means all worksheet functions are not
necessarily available in VBA macros?

And for reference, in case this was some weird thing that didn't show in
Intellisense but still worked in code (like the Print method of the
Printer object in VB6) I manually typed "IsBlank" and tried to run the
macro. I got an error saying the WorksheetFunction object didn't support
this method.

Ultimately, the problem I was trying to solve was one of "Excel being
Excel." I have a spreadsheet that has lots of blank cells (they don't even
contain spaces) which Excel doesn't THINK are blank cells. That is, when I
use the COUNTA() function on a range with these cells, they are counted
and not ignored. (If I simply go into edit mode in the cell and then hit
Enter, Excel wakes up and realizes the cell really is blank.) So what?
Well, I have some macros that do what they do until they encounter a blank
cell. I test for "blankness" by checking the Value of the cell against the
empty string. The problem is that these cells return the empty string for
their value but they're not really blank in Excel's eyes, so I'm looking
for a better way to detect them. If ISBLANK() were available in code then
I could check that instead.

IsBlank() in VBA code?
 

"Rick Rothstein (MVP - VB)" wrote in
message ...

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

I'm stunned at how active this group is! I no longer feel bad for directing
people here; I used to think this place was kind of dead.

The ISBLANK function only returns TRUE for a fully empty cell (it returns
FALSE if nothing is displayed as a result of a formula). If that is the
functionality you want in your VBA code, I think this function below will
work for you (just pass in a Range reference for the cell you are
testing)...

Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function

Notice, it is a one-liner.<bg

As expected!

The point is that the cell really is empty, but on workbook load, Excel
doesn't think so. As stated, if I simply double-click on it (or press F2)
and then hit Enter without doing anything else, Excel will "realize" that
the cell is empty. In other words, if A1 is one of these cells and I put
"=ISBLANK(A1)" into B1, B1 will display FALSE at first, but if I do the
"null edit" thing I just mentioned B1 will now show TRUE.

The Len() function, as mentioned by another poster, won't help, because as
far as VBA code is concerned, the Value of the cell is "". But ISBLANK()
definitely "knows" the cell isn't blank, or more specifically, it knows that
the COUNTA() function won't consider it blank. But since I can't use it in
code, I'm kind of stumped....

IsBlank() in VBA code?
 

See inline...

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

I'm stunned at how active this group is! I no longer feel bad for
directing people here; I used to think this place was kind of dead.

Not just this newsgroup. I regularly frequent m.p.e.programming and
m.p.e.worksheet.functions and they are equally active.

The point is that the cell really is empty, but on workbook load, Excel
doesn't think so. As stated, if I simply double-click on it (or press F2)
and then hit Enter without doing anything else, Excel will "realize" that
the cell is empty. In other words, if A1 is one of these cells and I put
"=ISBLANK(A1)" into B1, B1 will display FALSE at first, but if I do the
"null edit" thing I just mentioned B1 will now show TRUE.

The problem you are describing (COUNTA seeing a blank cell as not blank) is
not one I have seen before. Perhaps one of the regulars here has some
insight into your problem.

Rick

IsBlank() in VBA code?
 

I'm stunned at how active this group is! I no longer feel bad for
directing people here; I used to think this place was kind of dead.

Not just this newsgroup. I regularly frequent m.p.e.programming and
m.p.e.worksheet.functions and they are equally active.

DUH! We are in the m.p.e.programming newsgroup, aren't we. In that case, the
m.p.e.misc newsgroup (the one I thought we were in) is also quite active.

By the way, does the IsCellBlank function I posted see your blank cells as
blank in the same way ISBLANK is supposed to?

Rick

IsBlank() in VBA code?
 

"Jeff Johnson" wrote in message
news:p4CdnYaju_cbWpLVnZ2dnUVZ_uGdnZ2d@datapex...
"Rick Rothstein (MVP - VB)" wrote in
message ...

I'm assuming you are the Jeff Johnson I know from over in the compiled VB
newsgroups, right? What are you doing here... did you get lost?<g

I'm stunned at how active this group is! I no longer feel bad for
directing people here; I used to think this place was kind of dead.

Yeah, we like it here.

The ISBLANK function only returns TRUE for a fully empty cell (it returns
FALSE if nothing is displayed as a result of a formula). If that is the
functionality you want in your VBA code, I think this function below will
work for you (just pass in a Range reference for the cell you are
testing)...

Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function

Notice, it is a one-liner.<bg

As expected!

The point is that the cell really is empty, but on workbook load, Excel
doesn't think so. As stated, if I simply double-click on it (or press F2)
and then hit Enter without doing anything else, Excel will "realize" that
the cell is empty. In other words, if A1 is one of these cells and I put
"=ISBLANK(A1)" into B1, B1 will display FALSE at first, but if I do the
"null edit" thing I just mentioned B1 will now show TRUE.

The Len() function, as mentioned by another poster, won't help, because as
far as VBA code is concerned, the Value of the cell is "". But ISBLANK()
definitely "knows" the cell isn't blank, or more specifically, it knows
that the COUNTA() function won't consider it blank. But since I can't use
it in code, I'm kind of stumped....

Does it happen every time you open a workbook, or only the first time? It
sounds to me like what happens with imported data, where a "blank" may come
in as "", but upon "null editing" the cell, becomes an official blank cell.

I notice if I put ="" into a cell, ISBLANK() tells me the cell is not blank.
After I do this in the Immediate Window with that cell active:

ActiveCell.Value = ActiveCell.Value

then ISBLANK() tells me that the cell is blank. You might run this kind of
command on the appropriate region of the worksheet when it is first opened.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

IsBlank() in VBA code?
 

"Jon Peltier" wrote in message
...

Does it happen every time you open a workbook, or only the first time? It
sounds to me like what happens with imported data, where a "blank" may
come in as "", but upon "null editing" the cell, becomes an official blank
cell.

Yes, I'm almost positive the data was imported or perhaps pasted from the
result of an Access query.

I notice if I put ="" into a cell, ISBLANK() tells me the cell is not
blank. After I do this in the Immediate Window with that cell active:

ActiveCell.Value = ActiveCell.Value

then ISBLANK() tells me that the cell is blank. You might run this kind of
command on the appropriate region of the worksheet when it is first
opened.

Yeah, I know how to do that. The whole issue is not one of FIXING this
problem, but of finding an accurate way to DETECT it. It really just boils
down to Excel being a bit...eccentric, shall we say?

IsBlank() in VBA code?
 

To check if you have one of your non-blank blank cells, you can use this
formula...

=AND(ISTEXT(A1),A1="")

which seems to return TRUE for those cells. As for what you are trying to
do, based on some very skimpy testing, I think you can use this formula in
place of your COUNTIF one...

=SUMPRODUCT(--NOT(ISTEXT(A1:A6)*(A1:A6="")))

to count the cells with actual text in them.

Rick


"Jeff Johnson" wrote in message
...
"Rick Rothstein (MVP - VB)" wrote in
message ...

By the way, does the IsCellBlank function I posted see your blank cells
as
blank in the same way ISBLANK is supposed to?

Yes, it sees them as blank. But the problem is, that's not what I want. I
want to detect cases where the cell APPEARS to be blank but yet where
COUNTA() would count it. I guess I could simply use COUNTA() on the cell
and see if I get 1. I was just hoping for something more elegant.

In case you're curious, I chopped up the spreadsheet to leave only a small
sample of this phenomenon and attached it (yes, I know, the world will
end...) to this message.

IsBlank() in VBA code?
 

"Rick Rothstein (MVP - VB)" wrote in
message ...

To check if you have one of your non-blank blank cells, you can use this
formula...

=AND(ISTEXT(A1),A1="")

which seems to return TRUE for those cells.

Nice. And remember, I'm trying to do this from code, but I seem to recall
that IsText() is definitely a method of the WorksheetFunctions class.

IsBlank() in VBA code?
 

To check if you have one of your non-blank blank cells, you can use this
formula...

=AND(ISTEXT(A1),A1="")

which seems to return TRUE for those cells.

Nice. And remember, I'm trying to do this from code, but I seem to recall
that IsText() is definitely a method of the WorksheetFunctions class.

You can use this function to perform the same evaluation...

Function IsFakeBlank(R As Range) As Boolean
IsFakeBlank = Application.WorksheetFunction.IsText(R) And R.Value = ""
End Function

And guess what? It's another one-liner.<g Well, if we want to do it
correctly, I guess we need do need one more line...

Function IsFakeBlank(R As Range) As Boolean
If R.Count 1 Then Exit Sub
IsFakeBlank = Application.WorksheetFunction.IsText(R) And R.Value = ""
End Function

Can't forget about error checking. Anyway, just pass in the cell directly,
for example...

Sub Test()
Dim X As Long
For X = 1 To 6
Debug.Print IsFakeBlank(Cells(X, 1))
Next
End Sub


Rick

IsBlank() in VBA code?
 

Not sure if this string is still open, but I would like to see if I can
get this question answered. From what I'm reading this is what I'm
looking for to test blank cells.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String

If Range("C3") = "" Then
MsgBox "Date Missing. File is not Saved. Click OK to return to worksheet
to update."
Range("C3").Select
Exit Sub
ElseIf Range("C4") = "" Then
MsgBox "Job No. Missing. You must enter information in this field before
the file will be saved. Click OK to return to worksheet to update."
Range("C4").Select
Exit Sub
(there's more)
If the user presses the space bar Excel thinks it has something in the
field, but I want someone to enter words or a date in the field.

Can you explain where you put your code in VBA. I'm new to this and
never worked with functions. Also, I wasn't sure if your function worked
with selecting specific cells.

"Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function"

Thanks.




*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

One thing you can do is to use the Trim() function like the following line:

If VBA.Trim(Range("C3").Text) = "" Then

That way, the result of the above would remove any leading/trailing spaces.
This also works for when someone just put in spaces as it will remove the
spaces and return an empty string. The only spaces it doesn't remove will
be any spaces between any other characters within the value.

As a new person to programming, one of the first things you want to learn is
to avoid anything that is implicit (that implies and makes assumptions when
in fact those assumptions could be false) by nature. Example: The above
line of code is still implying the active worksheet as the worksheet you
want to have the Text property of Cell C3 checked. To avoid such an
implicit code, you may want to explicitly prequalify that Range Object with
a Worksheet Object in front of it and a Workbook Object in front of the
Worksheet Object like the following:

Workbooks("Book1.xls").Worksheets("Sheet1").Range( "C3").Text

By having it like that, it removes any assumptions that could be false.

Also, you will want to avoid using either the Select and/or Activate methods
as much as you reasonably can. There may be exceptions to using them in
cases that you don't have much of a choice, but those cases should be rare
in nature. I do have one such case, but it's as a result of the vendor of
our third party program (an add-in program we use within Excel for certain
functionalities) does not address this issue too well. I have contacted
them both by phone and by email including in response to an email with them
asking us as the customer if any improvements that could be done. In that
response in particular, I typed up a very detail response that got into what
are the issues it causes, which when I'm working on other stuff while my
reports are running within Excel, any time the code comes to the line of
"Workbooks(<WorkbookSpecName).Activate" (Note, the line inside the double
quotes, but not including the double quotes.), it deactivates the program
that I'm physically working in and activates Excel. If it activates the
spreadsheet side, not so bad as nothing too bad happens other than it's
rather irritating to be removed from the program that I was physically
working in. On the other hand, if it activates the VBA side, what few
characters I had typed past the point of time when the activation took place
may go into the VBA at it's cursor point, which then that can very well
being on a whole host of issues. If you are a 2 finger typer (hunt and peck
as some call it), then maybe that's not as much of an issue, but then it
could still be an issue as you are looking at the keyboard rather than at
the screen. On the other hand, if you are a typer like me that can type at
least 50 wpm (some may even get up to 90 to 100 wpm. I am around 50 to 60
wpm), then you can see how quickly that can also cause an issue. In that
email, I not only stated the problem, but I also gave them lines of how to
fix the issue and what sort of documentations would need to be put in. Of
course, the technical reps there who I have had contact from time to time
have acknowledged that my knowledge has exceeded their level of knowledge in
the program as many of them don't even remotely get into learning about the
events within the program where as I have it down to the point I not only
know how to use the events, but also know what order they fall in and what
are the various issues they have to fix with those different events, which I
have had to put in work arounds to address those different issues.

A couple more areas that you may want to think about doing.

One, for those objects you plan on calling on multiple times within the
code, you may want to assign them to object variables, so as to have the
code run more efficiently. However, also be sure to set those object
variables to the keyword of "NOTHING" when those object variables are no
longer of use anymore.

Also be sure to watch the naming of your various variables which the scheme
I use is the following:

<First letter of scope level & "_" & <3 letters to indicate type of
variable & <Readable variable name so as it's more or less self
documentation and easier to debug should that need to take place

Example:

Dim l_rngOrderDate As Range, l_dteOrderDate As Date

The reason for me to use this scheme, it allows for me to see what scope
it's at, This also helps the code itself as it eliminate a lot of confusion
that can happen otherwise. Not only can you see the scope, but you can also
see the data type instantly too. Also, as shown above, be sure to
explicitly declare all variables so as the code doesn't have a chance to
apply the incorrect data type to the variable name, and not only that, but
if you set the option to be explicit of all variables, it will allow for
proper compilation checks (a lot of errors can be caught via compilations
though you will still have run-time and logical type bugs to work out beyond
compilation test. Of course, you could assign variables either to Variant
or Object, but I don't like to use either one of these 2 for the same basic
reason along with the fact, it doesn't allow for the use of intellisense
while coding. As a matter of fact, if you get into VB.NET or any of the
languages in .NET programming, the Variant data type is no longer allowed.

I could go on, but figure this will be a good start for you as far as
learning VBA is concerned.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Jim McCaffrey" wrote in message
...
Not sure if this string is still open, but I would like to see if I can
get this question answered. From what I'm reading this is what I'm
looking for to test blank cells.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String

If Range("C3") = "" Then
MsgBox "Date Missing. File is not Saved. Click OK to return to worksheet
to update."
Range("C3").Select
Exit Sub
ElseIf Range("C4") = "" Then
MsgBox "Job No. Missing. You must enter information in this field before
the file will be saved. Click OK to return to worksheet to update."
Range("C4").Select
Exit Sub
(there's more)
If the user presses the space bar Excel thinks it has something in the
field, but I want someone to enter words or a date in the field.

Can you explain where you put your code in VBA. I'm new to this and
never worked with functions. Also, I wasn't sure if your function worked
with selecting specific cells.

"Function IsCellBlank(MyCell As Range) As Boolean
IsCellBlank = (MyCell.Value = "") And (MyCell.Formula = "")
End Function"

Thanks.




*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

Thank you Ronald for the quick repsonse and the VBA programming tips.
The Trim feature works perfectly. I have one more question (now that the
field validation works). Is it possible to allow the user to enter the
required information in the field that I take them to then after they
enter it continue on to check the next field?

If VBA.Trim(Range("C8").Text) = "" Then
MsgBox "Drawing Log field is empty. Click OK to return to worksheet to
update."
Range("C8").Select
Exit Sub
End If

If VBA.Trim(Range("C3").Text) = "" Then
MsgBox "Date field is missing. Click OK to return to worksheet to
update."
Range("C3").Select
End If

Thank you.

*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

One way to do this is to use an InputBox function, but there are a few
drawbacks to using it.

ColumnCCheck:
If VBA.Trim(Range("C8").Text) = "" Then
Range("C8").Select
Range("C8").Value2 = InputBox("Drawing Log field is empty. Enter a value
for this field.", _
"Data Missing")
Goto ColumnCCheck
End If

This method can work, but there are some draw backs to this route.

Method at this point doesn't perform a validation check other than checking
to see if it's either an empty string value or a null value, thus you may
still end up with invalid data in it.

For as long as the InputBox function is left as with no text in it, can be
caught in this infinite loop.

Depending on what part of the worksheet is showing, user may not see the
necessary data on the worksheet, and with VBA still in run mode, can't
scroll the worksheet or do anything else within that instance of Excel.

Still need to specify at least the worksheet prior to the range object,
especially if there is more than 1 worksheet within the workbook else it
could be looking at the C8 cell on the wrong worksheet.

Still need to specify the Workbook in front of the worksheet objects cause
if AutoSave is turned on within the User's instance of Excel, and the user
is working in another workbook, this code will either error out or it will
be looking in the wrong workbook.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Jim McCaffrey" wrote in message
...
Thank you Ronald for the quick repsonse and the VBA programming tips.
The Trim feature works perfectly. I have one more question (now that the
field validation works). Is it possible to allow the user to enter the
required information in the field that I take them to then after they
enter it continue on to check the next field?

If VBA.Trim(Range("C8").Text) = "" Then
MsgBox "Drawing Log field is empty. Click OK to return to worksheet to
update."
Range("C8").Select
Exit Sub
End If

If VBA.Trim(Range("C3").Text) = "" Then
MsgBox "Date field is missing. Click OK to return to worksheet to
update."
Range("C3").Select
End If

Thank you.

*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

Thanks for your help Ron. It works great. Here's part of the code I'm
using. Just one last question - what does ColumnCCheck mean?

Regards,
Jim

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String

'with help from Ronald R. Dodge, Jr. Developersdex.com

ActiveSheet.Unprotect Password:=
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
UserInterfaceOnly:=True, Scenarios:=True _
, AllowFormattingCells:=False, AllowFormattingRows:=False, Password:=

ColumnCCheck:
If VBA.Trim(Worksheets("DLR").Range("C3").Text) = "" Then
Range("C3").Select
MsgBox "Date Field is empty. Click OK to return to worksheet to
update."
Range("C3").Value2 = Application.InputBox("Date field is empty.
Enter a date for this field.", "Date_Field", "mm-dd-yyyy")
GoTo ColumnCCheck
End If

ColumnCCheck1:
If VBA.Trim(Worksheets("DLR").Range("C8").Text) = "" Then
Range("C8").Select
MsgBox "Drawing Log field is empty. Click OK to return to worksheet
to update."
Range("C8").Value2 = Application.InputBox("Drawing Log field is
empty. Enter a value for this field.", "Data_Missing")
GoTo ColumnCCheck1
End If
...more


*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

The part of the code that you see as:

ColumnCCheck:

It's considered as a Label within the code, so where you see the "Goto"
statement lower in the code, it will take the cursor back up to that label
line and start going down again line by line. You can use what ever name
you feel appropriate, but be sure to stay consistent with it. Sorry for the
late response as I been working on a major project myself, some of which
also involves learning the last bit of how events works within the OOP
environment of VBA.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Jim McCaffrey" wrote in message
...
Thanks for your help Ron. It works great. Here's part of the code I'm
using. Just one last question - what does ColumnCCheck mean?

Regards,
Jim

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String

'with help from Ronald R. Dodge, Jr. Developersdex.com

ActiveSheet.Unprotect Password:=
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
UserInterfaceOnly:=True, Scenarios:=True _
, AllowFormattingCells:=False, AllowFormattingRows:=False, Password:=

ColumnCCheck:
If VBA.Trim(Worksheets("DLR").Range("C3").Text) = "" Then
Range("C3").Select
MsgBox "Date Field is empty. Click OK to return to worksheet to
update."
Range("C3").Value2 = Application.InputBox("Date field is empty.
Enter a date for this field.", "Date_Field", "mm-dd-yyyy")
GoTo ColumnCCheck
End If

ColumnCCheck1:
If VBA.Trim(Worksheets("DLR").Range("C8").Text) = "" Then
Range("C8").Select
MsgBox "Drawing Log field is empty. Click OK to return to worksheet
to update."
Range("C8").Value2 = Application.InputBox("Drawing Log field is
empty. Enter a value for this field.", "Data_Missing")
GoTo ColumnCCheck1
End If
..more


*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

Ronald - I wonder if you could help me with one other thing. I have a
couple required fields that contain several lines of text and I'm trying
to make the Application.InputBox bigger, but I can't find anything on
changing the size. I started to create a user form, but I keep getting a
"mismatch" error. I also added a Do Loop statement so that the user has
to enter something in the field.

Thanks again for your help.
Jim

ColumnCCheck5:
If VBA.Trim(Worksheets("DLR").Range("F11").Text) = "" Then
Range("F11").Select
MsgBox "Work Performed field is empty. Click OK to return to
worksheet to update."

Do
' Range("F11").Value2 = UserForm1.Show

Range("F11").Value2 = Application.InputBox("Work Performed field is
empty. Enter the Work Performed that day in this field. You can enter a
little bit of information then go back later and add more. ", "Work
Performed")

Loop Until Range("C8").Value2 < "False"
GoTo ColumnCCheck5
End If



*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

I'm not sure how this code is run, but this may get you closer.

And I'm gonna guess that you want to use this userform to populate the
activecell--not just F11.

'select the cell to start

with activecell
if trim(.value) = "" then
userform1.show
end if
end with

Then I created a userform that has a label, a textbox and two commandbuttons.

This is the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
ActiveCell.Value = Trim(Me.TextBox1.Text)
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Me.CommandButton1.Enabled _
= CBool(Len(Trim(Me.TextBox1.Text)) 0)
End Sub
Private Sub UserForm_Initialize()

Me.Caption = "Enter a value"

With Me.Label1
.Caption = "Please enter a value for: " _
& ActiveCell.Address(0, 0)
.ForeColor = vbRed
End With

With Me.CommandButton1
.Caption = "Ok"
.Default = True
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.TakeFocusOnClick = False
End With

End Sub

The Ok button should only be "clickable" if there's something (non-space) in
that textbox.

Debra Dalgleish shares lots of good info about userforms:
http://contextures.com/xlUserForm01.html




Jim McCaffrey wrote:

Ronald - I wonder if you could help me with one other thing. I have a
couple required fields that contain several lines of text and I'm trying
to make the Application.InputBox bigger, but I can't find anything on
changing the size. I started to create a user form, but I keep getting a
"mismatch" error. I also added a Do Loop statement so that the user has
to enter something in the field.

Thanks again for your help.
Jim

ColumnCCheck5:
If VBA.Trim(Worksheets("DLR").Range("F11").Text) = "" Then
Range("F11").Select
MsgBox "Work Performed field is empty. Click OK to return to
worksheet to update."

Do
' Range("F11").Value2 = UserForm1.Show

Range("F11").Value2 = Application.InputBox("Work Performed field is
empty. Enter the Work Performed that day in this field. You can enter a
little bit of information then go back later and add more. ", "Work
Performed")

Loop Until Range("C8").Value2 < "False"
GoTo ColumnCCheck5
End If

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

IsBlank() in VBA code?
 

Dave - thanks for the information. I'm trying to implement the code you
created and I get an error in the following section - Label1 (method or
data member not found). I wasn't sure where to put the code, so I
created a UserForm1 and put the code there. Is that the right location?

(Private Sub UserForm_Initialize()

Me.Caption = "Enter a value"

With Me.Label1
.Caption = "Please enter a value for: " _
& ActiveCell.Address(0, 0)
.ForeColor = vbRed)
---
This is what I am using to call the UserForm:

If Trim(Range("F11").Value) = "" Then
UserForm1.Show
End If
---


Thanks again.

Jim




*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

I'm guessing that you didn't add a label to the userform.

Jim McCaffrey wrote:

Dave - thanks for the information. I'm trying to implement the code you
created and I get an error in the following section - Label1 (method or
data member not found). I wasn't sure where to put the code, so I
created a UserForm1 and put the code there. Is that the right location?

(Private Sub UserForm_Initialize()

Me.Caption = "Enter a value"

With Me.Label1
.Caption = "Please enter a value for: " _
& ActiveCell.Address(0, 0)
.ForeColor = vbRed)
---
This is what I am using to call the UserForm:

If Trim(Range("F11").Value) = "" Then
UserForm1.Show
End If
---

Thanks again.

Jim

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

IsBlank() in VBA code?
 

Dave - please ignore my last post. I have too many things going on and I
wasn't completing the UserForm. I added the label and it works fine. I
removed the Cancel button because I want the user to enter something in
the field. I need to add word wrap to the UserForm and I should be set.

Thank you.


*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

Just a warning...

As a user, I would appreciate the cancel button (or learn to use the X on the
title bar).

There are lots of times where I've started something in error--or had to go do
something (find the value elsewhere (maybe copy|paste from some other location
in excel) or just go to a meeting).

I'd want a way to dismiss that dialog.

And some code that may help:

Option Explicit
Private Sub CommandButton1_Click()
ActiveCell.Value = Replace(Trim(Me.TextBox1.Text), vbNewLine, vbLf)
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Me.CommandButton1.Enabled _
= CBool(Len(Trim(Me.TextBox1.Text)) 0)
End Sub
Private Sub UserForm_Initialize()

Me.Caption = "Enter a value"

With Me.Label1
.Caption = "Please enter a value for: " _
& ActiveCell.Address(0, 0)
.ForeColor = vbRed
End With

With Me.CommandButton1
.Caption = "Ok"
.Default = True
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.TakeFocusOnClick = False
End With

With Me.TextBox1
.EnterKeyBehavior = True
.MultiLine = True
End With

End Sub



Jim McCaffrey wrote:

Dave - please ignore my last post. I have too many things going on and I
wasn't completing the UserForm. I added the label and it works fine. I
removed the Cancel button because I want the user to enter something in
the field. I need to add word wrap to the UserForm and I should be set.

Thank you.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

IsBlank() in VBA code?
 

I have been reading a book "Excel 2007 VBA" by John Green and others and
they make the point that the Evaluate method can make available
worksheet functions that are not made available through the
WorksheetFunction Object.

He illustrates this with the following code

Sub Test()
Dim sFunctionName As String, sCellReference As String
sFunctionName = "=ISBLANK"
sCellReference = ActiveCell.Address
MsgBox Evaluate(sFunctionName & "(" & sCellReference & ")")
End Sub

The equal sign (=) in "=ISBLANK" is optional

I have not fully tested this but it does report a cell with a blank
space as not being blank.


*** Sent via Developersdex http://www.developersdex.com ***

IsBlank() in VBA code?
 

It's testing the active cell. Maybe you changed to a different cell when you
were testing.

If you add another msgbox before the End Sub:

MsgBox "**" & ActiveCell.Text & "**" & vbLf & ActiveCell.HasFormula

What do you see?



Max Hilbig wrote:

I have been reading a book "Excel 2007 VBA" by John Green and others and
they make the point that the Evaluate method can make available
worksheet functions that are not made available through the
WorksheetFunction Object.

He illustrates this with the following code

Sub Test()
Dim sFunctionName As String, sCellReference As String
sFunctionName = "=ISBLANK"
sCellReference = ActiveCell.Address
MsgBox Evaluate(sFunctionName & "(" & sCellReference & ")")
End Sub

The equal sign (=) in "=ISBLANK" is optional

I have not fully tested this but it does report a cell with a blank
space as not being blank.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

IsBlank() in VBA code?
 

I'm also with Dave on this. I have been in 3 of 4 arenas with databases.
The 4 arenas would be the following:

Data Transcriber (I got this experience from when I worked at the IRS
entering data into the system [hence where the name of this arena comes
from] and hated it cause of the break situation at the IRS working the 10
hour shift, which then led to early stages of carpal tunnel syndrome before
I got out of it. Programming was my ticket out of this work.)

Data User (I am very much so in this arena as I rely on the data that is put
into the system. I know I am at times very bull headed about the accuracy
of the data, thus part of the reasons why I put in checks like I have with
the programs I have put into place. Of course the programming work doesn't
fall in this arena, but as a user of the data, the more accurate the data
is, the better of a decision others and I can make.)

Database Programmer (I have had to learn how to build a database and go from
there. However, given our work environment and the limitations of Access, I
can't rely on using Access, so best I can do with Access is use it as a
prototype. For what ever reason, Access losses connection to the server
during the time period when backups are taking place on the server, and we
can't expect our night shift operators to have to restart not only the
program, but also the system nightly on older slower systems. Not only
that, but they don't even get notice of it until they attempt to do
something after connection has been disrupted to the point that it can't be
restored until the system is restarted. For someone in this arena, they
must learn the 6 normalization rules [1NF, 2NF, 3NF, BCNF, 4NF and 5NF],
SQL, how to integrate with other programming languages for the short falls
of SQL, and working with forms with the proper validation codes and in many
cases, learning the events and how to use those events.)

Database Administrator (While I may have some basic experience, I don't have
the official experience in this arena. I have dealt with things that
impacts this arena, so it wouldn't be that hard for me to move into this
arena. One of the areas I have dealt with is security and I really don't
like the security model of Access either on the account of too many issues
with it.)


As a data transcriber, I would like the option at the minimal to back out of
the form just like what Dave said. However, as both a data user relying on
the accuracy of the data and as a database programmer, I would want to have
code put in place to verify the data with all of the necessary checks
(reasonably speaking) before allowing the data transcriber continue on.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Jim McCaffrey" wrote in message
...
Dave - thanks for the information. I'm trying to implement the code you
created and I get an error in the following section - Label1 (method or
data member not found). I wasn't sure where to put the code, so I
created a UserForm1 and put the code there. Is that the right location?

(Private Sub UserForm_Initialize()

Me.Caption = "Enter a value"

With Me.Label1
.Caption = "Please enter a value for: " _
& ActiveCell.Address(0, 0)
.ForeColor = vbRed)
---
This is what I am using to call the UserForm:

If Trim(Range("F11").Value) = "" Then
UserForm1.Show
End If
---


Thanks again.

Jim




*** Sent via Developersdex http://www.developersdex.com ***