PDA

View Full Version : Solved: Checking For Text In A Cell



drums4monty
11-29-2006, 04:56 PM
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
11-29-2006, 05:19 PM
In the ThisWorkbook code module...

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

Zack Barresse
11-29-2006, 09:37 PM
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
11-29-2006, 10:03 PM
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)'assumes you're printing the active sheetThey 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
11-30-2006, 02:21 AM
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
11-30-2006, 02:30 AM
Yes, that will do it - it's not a problem :)

Bob Phillips
11-30-2006, 02:53 AM
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

johnske
11-30-2006, 03:21 AM
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
Seems wordy, what's wrong with? 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 SubBTW, the OP's said there's only worksheets in the workbook

Edit: (amended)

drums4monty
11-30-2006, 08:34 AM
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.

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

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

Regards

Alan

johnske
11-30-2006, 08:39 AM
It has to go in the ThisWorkbook code module - look in the project explorer and click ThisWorkbook... :)

drums4monty
11-30-2006, 08:59 AM
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
11-30-2006, 09:13 AM
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
11-30-2006, 09:28 AM
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
11-30-2006, 09:37 AM
nope, and that error probably means you have 2 procedures with the same name. Got to get rid of one of them.

lucas
11-30-2006, 09:38 AM
It would help if you would post a sample workbook so others could see what your trying to do.......just a thought.

johnske
11-30-2006, 09:41 AM
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 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

drums4monty
11-30-2006, 12:27 PM
Thanks all, I have it working, albeit it not exactly how I wanted but it does the job.

Regards

Alan

Bob Phillips
11-30-2006, 12:41 PM
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
11-30-2006, 01:55 PM
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
Uh huh, sure... hehe

Bob Phillips
11-30-2006, 06:06 PM
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.

johnske
11-30-2006, 10:36 PM
Hi Alan,

Good to see you've found something that works for you.

What I gave you was a workbook "event" procedure (the event in this case being that you have clicked the "Print" icon) - so Private Sub Workbook_BeforePrint(Cancel As Boolean) will be called before printing.

If you want this procedure to work when you press the Print icon, it must be in the ThisWorkbook code module of the workbook you're using to call for a print, and it must be declared exactly as written i.e. Private Sub Workbook_BeforePrint(Cancel As Boolean) as you will get a Compile error (Procedure declaration does not match description event or procedure having the same name) if you don't.

I was not originally aware that this was to be used in other workbooks - the problem here is that event code is restricted to operate only when an event occurs in the workbook in which it resides, so it can't be called from Personal.xls.

Of course there are always ways around this, but then there are always further problems or complications with work-arounds...

For instance, you could place the event code in Personal.xls, then your first piece of code could be modified to copy the sheet you're printing onto a worksheet in Personal.xls, then perhaps make Personal.xls the active and visible workbook so that when you click the Print icon the event code kicks in. Unfortunately, the complication is that the printing restriction you've given will then be applied to every workbook whether it's wanted or not.

Another work-around may be to place your code and the event code into a special workbook that's only to be used when you want these restrictions to apply. This workbook could then be opened 1st, with the Before_Print event code to then be copied into any other workbooks 'ThisWorkbook' code module.

Another option may be to delete the Print icon and replace it with a custom Print icon to intercept the 'Print' command, or, place a floating custom toolbar to be used for the print.

But all of these are complicated options and, for your purposes, it seems that it would be much simpler to just check that the data field has an entry when you run your original piece of code (as you've already done).

BTW: I very strongly recommend that you always use Option Explicit. (The fact that you used 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 Suband didn't get an error message with "Cancel" highlighted tells me you didn't :))

John

johnske
11-30-2006, 10:44 PM
@ EL XID: ??? Bob, might I suggest you focus more on function and less on (my) style? In my own opinion it's also pretty lazy sloppy practice to use a 'crash and burn' technique to check whether the active sheet is a worksheet when - as I've shown - you can simply use the 'Type' object to check for it.

@ FireFly: Zack, at face value this was a very simple question to which I originally responded with a very simple workable solution, leaving it to the OP to decide if that was what was actually required by them (or not) or if it needed mods and/or any error-handling. In fact there are just so many examples on this board of a 1st basic solution being posted and then being modified to suit the OPs requirements that it could very well be regarded as a 'standard practice' for the board.

@ FireFly: (quote) "It might not be needed now, but the possibilities of it being needed in the future are (IMO) stunningly high." - So there really shouldn't be any problem for you to provide me with just one example then... :)

@ EL XID and FireFly: So what is this - when you sign on as an MVP do you have to swear an oath of allegiance to follow someones idea of what is 'best practice' and apply those rules even for cases where it simpy doesn't apply? - Or are this boards MVPs just taking an opportunity to gang up on me? :)


Comment re SN (aka Evaluate): I could say so much, but I'll restrict myself to this:

Might I suggest that the team of Microsoft coders that wrote and documented the underlying code that we know as 'Visual Basic for applications' (and the VBA Help files) may just perhaps know maybe a teeny little more about it than absolutely anyone else? i.e. whatever objects, methods, etc., they've included there for our use, that team of experts made a carefully reasoned TEAM decision that all of these are somehow necessary or essential - and I merely use the tools they've provided.

But, whatever personal opinion you may have of SN, at the end of the day the fact is that it is documented in both the Help files and the MSDN library and in all the documentation (and quite unlike some other documented things, such as Wend, ElseIf,..., etc. statements) there is not even the slightest hint that SN should not be the preferred option.

Note that I also have my personal aversions e.g. using the Cells property (with its Row, Column style of referencing) to refer to single cells when (IMO) the Range property (using the much more commonly used Column, Row style of referencing) would often be better employed makes me groan in pain - but as a rule I keep my opinion to myself and suffer in silence (hint)

drums4monty
12-01-2006, 02:03 AM
Going on from what I have already, would it be possible to do the following:

I have four sheets in the workbook and each needs to be checked for a name in B3.

Check that here is a name in B3, if not give me a warning and an input box so that I can input the name myself, then move onto the next sheet, do the same, and so on. There is no need to worry about printing now as this can be done manually once I know all the sheets have a name in B3.

The sheets are named, Uniform, Stationary, Retail and Name Badges.

Regards

Alan

drums4monty
12-01-2006, 02:23 AM
Wow, cancel my last request, I have managed to work it out myself.

Alan

Zack Barresse
12-01-2006, 12:23 PM
John, I was not intending to flame anybody here. I only meant to tell the OP about some common pitfalls and - since they know their material better than most - give them some things to think about which might work better for them now and in the future. This is all I will say in this thread, as anything else will hijack it.