Solved: Checking For Text In A Cell

[drums4monty]

I run some code in a worksheet (which members of this forum have helped me with) to format the sheet when the code is run. What I need to do now is check whether some text i.e. a name is in cell B3, I have two choices here, it either needs to do the check and warn me if no text is in the cell after the code to format the sheet has run, or warn me when I have clicked the print button. This is so I do not print the sheet without text being present. Is this possible?

Regards

Alan

[johnske]

In the ThisWorkbook code module...
[VBA]
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'assumes you're printing the active sheet
If [B3] = Empty Then
Cancel = True
[B3].Activate
MsgBox "A name is required in the selected cell"
End If
End Sub
[/VBA]

[Zack Barresse]

Ugh, you and your shorthand notation..

It is best to specify (or test for) the sheet(s) desired. The code will fail if a chart sheet is active. Also, when using events in Excel, it is best to set the Application.EnableEvents property to False to prevent any other events from firing while in mid-run.

[johnske]

Ugh, you and your shorthand notation..

I don't enjoy the luxury of being a touch typist

It is best to specify (or test for) the sheet(s) desired.The code will fail if a chart sheet is active.

Naturally - and the comment of course refers to WORKsheet - the OP said they were using code to do other things such as formatting cells, the implication is thus that this is a worksheet that's going to be printed and any testing for chart sheets has already been done in that code (where I'd also assume they're probably calling for the print from within that code)[vba]'assumes you're printing the active sheet[/vba]They can modify to suit if ActiveSheet's not the case, and post back if there's any problems.

Also, when using events in Excel, it is best to set the Application.EnableEvents property to False to prevent any other events from firing while in mid-run.

Not really applicable in this case

EDIT: BTW, it's in no way "my" shorthand notation, it belongs to - and was included in Visual Basic by - Microsoft

[drums4monty]

HI Johnske

Thanks for the code, Ive got that working fine. My only problem (if it is a problem) is that I do not print within the sheet, I click the print button on the toolbar. I don't print within the sheet as there are 4 sheets and if any sheets do not contain data I do not print them. All 4 sheets are formated with my code then I check sheet 1 for data then print, then check sheet 2 then print etc. I just need a way that when I click the Print Icon, if a name does not exist in B3 it will stop and tell me.

Alan

[johnske]

Yes, that will do it - it's not a problem

[Bob Phillips]

[vba]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet

On Error Resume Next
Set sh = ActiveSheet
On Error GoTo 0

'if the activesheet is a worksheet, check it
If Not sh Is Nothing Then
If sh.Range("B3").Value = "" Then
Cancel = True
sh.Range("B3").Activate
MsgBox "A name is required in the selected cell"
End If
End If
End Sub
[/vba]

[johnske]

[vba]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sh As Worksheet

On Error Resume Next
Set sh = ActiveSheet
On Error GoTo 0

'if the activesheet is a worksheet, check it
If Not sh Is Nothing Then
If sh.Range("B3").Value = "" Then
Cancel = True
sh.Range("B3").Activate
MsgBox "A name is required in the selected cell"
End If
End If
End Sub
[/vba]

Seems wordy, what's wrong with? [vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)
'assumes you're printing the active sheet
If ActiveSheet.Type = xlWorksheet Then
If [B3] = Empty Then
Cancel = True
[B3].Activate
MsgBox "A name is required in the selected cell"
End If
End If
End Sub[/vba]BTW, the OP's said there's only worksheets in the workbook

Edit: (amended)

[drums4monty]

HI xld & Johnske

Here is the end bit of my format code. I cannot get your code to run unless I change it like this. This may be to me not knowing how to use a Privare Sub etc.

[VBA]Sub removeblanks()
'
' Deletes all rows which is either blank or has a 0 (xero) in it
Dim Rng As Range
Dim MyCell
Dim i
Set Rng = Range("C9:C119")
With Rng
Set c = .Find(0, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
Call Workbook_BeforePrint
End Sub

Sub Workbook_BeforePrint()
'assumes you're printing the active sheet
If [B3] = Empty Then
Cancel = True
[B3].Activate
MsgBox "Club Name Is missing"
End If
Call FormatAll
End Sub

Sub FormatAll()
' FormatAll Macro
' Macro recorded 18/11/2006 by Alan
' Moves onto Retail Sheet
'
Sheets("Retail").Select
Application.Run "PERSONAL.XLS!FormatSheetRetail"
End Sub[/VBA]

In this way it works but I really would like it to perform an pressing the print icon.

Regards

Alan

[johnske]

It has to go in the ThisWorkbook code module - look in the project explorer and click ThisWorkbook...

[drums4monty]

Does that mean it has to be in the workbooks that people send to me or can it be on my pc to use on their workbooks?

[lucas]

In the visual basic editor...where you have your modules.
Look on the left side for the project explorer.
If it is not visable..make it visable by going to the main menu look for view and then click on the project explorer.
in the project explorer look for the ThisWorkbook Module under the project you have open(in other words if its book1.xls look under that project for the thisWorkbook module. Click on it and place your before print code there....

[drums4monty]

Done that and now I get an error that says 'Compile error: Procedure declartation does not match description event or procedure having the same name'

Is there not a way of doing the same thing from a Module in the Personal.xls ?

Alan

[lucas]

nope, and that error probably means you have 2 procedures with the same name. Got to get rid of one of them.

[lucas]

It would help if you would post a sample workbook so others could see what your trying to do.......just a thought.

[johnske]

No, not in Personal, it has to be in the ThisWorkbook code module of the workbook you're going to print from. And you'll get a compile error if you use the version you've modified in your code above, use [vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)
'assumes you're printing the active sheet
If ActiveSheet.Type = xlWorksheet Then
If [B3] = Empty Then
Cancel = True
[B3].Activate
MsgBox "A name is required in the selected cell"
End If
End If
End Sub[/vba]

[drums4monty]

Thanks all, I have it working, albeit it not exactly how I wanted but it does the job.

Regards

Alan

[Bob Phillips]

BTW, the OP's said there's only worksheets in the workbook

Edit: (amended)

You could rely on that always being the case, but it doesn't take much to cater for it.

And I won't even bother responding to the other statement.

[Zack Barresse]

Naturally - and the comment of course refers to WORKsheet - the OP said they were using code to do other things such as formatting cells, the implication is thus that this is a worksheet that's going to be printed and any testing for chart sheets has already been done in that code (where I'd also assume they're probably calling for the print from within that code)

I know it is a worksheet, and I realize all the sheets in the workbook might be worksheets. But this does not preclude the fact that somebody else may come along in the future and adjust the workbook. Possibly somebody who does not know code. What happens then? We will see a post in the forums with this same issue coming back up again. It only makes sense to warn against all probably failures.

Not really applicable in this case

Disabling events? This goes back to best practices. It might not be needed now, but the possibilities of it being needed in the future are (IMO) stunningly high. Prevention before reaction.

EDIT: BTW, it's in no way "my" shorthand notation, it belongs to - and was included in Visual Basic by - Microsoft

[uvba]Uh huh, sure... hehe[/uvba]

[Bob Phillips]

I know it is a worksheet, and I realize all the sheets in the workbook might be worksheets. But this does not preclude the fact that somebody else may come along in the future and adjust the workbook. Possibly somebody who does not know code. What happens then? We will see a post in the forums with this same issue coming back up again. It only makes sense to warn against all probably failures.

That's what I said.

Disabling events? This goes back to best practices. It might not be needed now, but the possibilities of it being needed in the future are (IMO) stunningly high. Prevention before reaction.

I agree. It is a lazy sloppy practice not to add it, it takes so little effort. Just like shorthand notation.