Redefining ranges

I use the following code that will not allow an end user to close a
spreadsheet until all specified cells are entered.

Private Sub WorkBook_BeforeClose(Cancel As Boolean)
      If Application.WorkSheetFunction.CountA(Sheets("Sheet1") _
      .Range( "Customer" ) <8 Then
      MsgBox " You must fill in all cells"
      Cancel = True
End If
End Sub

Now it works okay with one sheet but we have hundreds of sheets with more
added daily. How can I restructure the code to redefine the ranges for each
sheet ( the sheets being identical ) and that as each new sheet is added it
would be updated to reflect its range and new sheet name? I hope I am making
sense. Thank you.



0
bjohnson (28)
10/11/2003 1:42:50 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
561 Views

Similar Articles

[PageSpeed] 31

Try this

Private Sub WorkBook_BeforeClose(Cancel As Boolean)

For i= 1 to Sheets.count
      If Application.WorkSheetFunction.CountA(Sheets(i) _
      .Range( "Customer" ) <8 Then
      MsgBox " You must fill in all cells - data missing in " &
Sheets(i).name
      Cancel = True
      End If
Next i
End Sub

I have assumed that Customer is a named range that applies to any sheet

This will produce a separate msgbox for each sheet with data missing which
could be tiresome but may be what you want.

Geoff






"Carl Johnson" <bjohnson@woh.rr.com> wrote in message
news:ueJhb.117044$xx4.19181501@twister.neo.rr.com...
> I use the following code that will not allow an end user to close a
> spreadsheet until all specified cells are entered.
>
> Private Sub WorkBook_BeforeClose(Cancel As Boolean)
>       If Application.WorkSheetFunction.CountA(Sheets("Sheet1") _
>       .Range( "Customer" ) <8 Then
>       MsgBox " You must fill in all cells"
>       Cancel = True
> End If
> End Sub
>
> Now it works okay with one sheet but we have hundreds of sheets with more
> added daily. How can I restructure the code to redefine the ranges for
each
> sheet ( the sheets being identical ) and that as each new sheet is added
it
> would be updated to reflect its range and new sheet name? I hope I am
making
> sense. Thank you.
>
>
>


0
10/11/2003 5:09:00 AM
Reply:

Similar Artilces:

Pasting data top 1st empty cell in range
Hi, am trying to past data from selection to first empty cell in a range. Have worked around it for simple sheet just by inserting new row, but this isn't any good for a sheet that has columns being totalled... The aim is to hit the button, paste selected data into first empty cell in range, but if all cells populated to go and do the same in a different range - eg a continuation page. The reason for this being that if I add new rows, the print range changes and will no longer fit onto the page (this is an invoice with subtotals/totals that are cleared when page is full and the 2nd page...

Date Range on report
Hello, I have a query that asks for a "begining date" and "end date" as parameters. Is there anyway to poulate these two dates on a report so that the user knows the time frame (they just keyed) of the report? I'm trying to summarize results for employees by week. You can refer to the parameters in the report. You can set the ControlSource property for a text box to something like: ="Reporting between " & [Beginning Date] & " and " & [End Date] -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) &qu...

use cell reference for named range
I have named several ranges on my sheet with names of cities such a "newYork", "Chicago", "sanDiego" etc. I would like to use these ranges in a formula, but rather than type i these ranges I would like to use a cell reference that contains thes names. For example, instead of typing =COUNT(Chicago) where "Chicago" is range I have defined, I would like to be able to type something lik =COUNT(A20) where A20 contains the text Chicago. I hope I am making myself clear. Thank you -- elf2 -----------------------------------------------------------------...

Source Range
I have a sheet with four columns of data. The first column is date/time information for the X-Axis. Every day, two new rows are added at the bottom. I have to manually change the source data range each time to include these newly added rows of data. Is there a way to specify that all rows containing data are to be included? I could specify, say, one-hundred additional rows but that would create a large empty area at the right side of the chart and unnecessarily compress the data to the left. Is there an alternate solution? Thanks in advance for any comments, /s/ Alan Auerbach http://pe...

How to Select a relative range with Using "Find" and Offset()
XL 2003 The following works fine: Sub OneCellText() Dim MyRange As Range Dim MyCell As Range Dim TempVar As String Set MyRange = Selection For Each MyCell In MyRange If MyCell.Value <> "" Then TempVar = TempVar + MyCell.Value + Chr(10) Next MyCell Range("E41").Formula = TempVar End Sub I would like to "compute" MyRange as follows: Cells.Find(What:="IMPACTED ACCOUNTS").Activate ActiveCell.Offset(1, 3).Range("A1:E10").Select Where things get tough is that the W/S that I review have ...

variable range in a macro
I have a worksheet where the number of rows and columns vary each time I process the data. I want to transpose the cols and rows but need to set the range before I copy. There may not be any data in the XY cell. Is there a way to do this in a macro? Thanks for any help. -- Al Hi Al, Assume data starts in A1 and that there is a blank row below, and a blank column to the right, of the data, then your data can be defined by: ActiveSheet.Range("A1").CurrentRegion --- Regards, Norman "AMK" <AMK@discussions.microsoft.com> wrote in message news:2ACC5FE1-...

XMLDataDocument.DataSet.ReadXML and redefining elements in an XSD file with <xs:redefine>
Hi, The environment is .NET 2.0, the language is C# and the problem is reading XSD file with xs:redefine section correctly to a XMLDataDocument.DataSet. What I am trying to do: I am trying to create a DataSet object from an XSD file by using XMLDataDocument class. I'm using the method "XMLDataDocument.DataSet.ReadXmlSchema(XSDfilelocation)". The XSD file that I am reading contains an <xs:redefine schemalocation="some other schema definition.xsd"> section that points to another XSD file. The problem: The problem is that when the DataSet object is created, the ...

range changing
Hello all: How do I prevent a range from changing/shifting no matter what I do t the worksheet. I have range from column D to M, my table has data from column A to M. After highlighting column D to M, I put the name "CINAME" in the nam box. But this range changes to C to L when I copy a new data to th sheet and run the macro on the sheet. I found this formula: =OFFSET(INDIRECT("Sheet1!$A$1"),0,0,CountA(Sheet1!$A:$A),CountA(Sheet1!$1:$1)) Would this prevent my range and the whole table from shifting if I g to Insert/Name/Define and put the formula in the "Refers to...

Pivot tables, linking to a named range as a source to a pivot table
I have created a main worksheet within the same workbook of many pivot tables, and I want to use this same worksheet as the source of information to these different pivot table sheets off of which I create charts. I want to use a named range because there are over 4,000 rows in this main worksheet. I am not sure if I need a "=" to start the reference or what to do. I thought I should just use nameofworksheet!database if I name the range "database", but when I point and click to the sheet, it is just inserting the name "database" without the name of the...

Redefine Table Array
I already have set up a Vlookup but i want to add more items to this how to i make the range include the newly added data I like to put my table on a dedicated worksheet. Then I can use the whole column without worrying. =vlookup(a1,sheet2!a:e,3,false) But you could use a dynamic range name that grows/extracts with your data. Take a look at Debra Dalgleish's site to read more about it: http://www.contextures.com/xlNames01.html#Dynamic Shaya M wrote: > > I already have set up a Vlookup but i want to add more items to this how to i > make the range include the newly added da...

Change Range in macro
How can I change the range in the macro below for sending email to recpients, I want to change the range to sheet3 and email addresses that are input into A1:A10. Here's the code: Sub Mail_ActiveSheet() 'Working in 2000-2010 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcew...

Assign one number to a number range
For example, -assign 1 to any number between 50 and 64 -assign 2 to any number betwen 65 to 79 -assign 3 to any number between 80-100 So that 62 (in column 1) will be coded as a 1 (in column 2), whereas 67 would be coded a 2 and 83 would be coded a 3. In cell B1 put this formula and copy down.......... =IF(OR(A1<50,A1>100),"Out of range",IF(A1>=80,3,IF(A1>=65,2,IF(A1>=50,1)))) Vaya con Dios, Chuck, CABGx3 "Sue" <Sue@discussions.microsoft.com> wrote in message news:B436CAB2-1A68-46A0-BA9E-28C7B0A22B68@microsoft.com... > For example, > -as...

Redefining a shape...
I created a custom shape... and used it in a drawing. Now I have to edit the shape. Is there a way to have all instances of that shape take on the new attributes? Similar to paragraph styles is Word....... edit it in the document stencil al "Larry" <larry.dare@gmail.com> wrote in message news:1145028732.047809.295360@g10g2000cwb.googlegroups.com... >I created a custom shape... and used it in a drawing. > Now I have to edit the shape. > > Is there a way to have all instances of that shape take on the new > attributes? > > Similar to paragraph styles ...

Number Range Format Type
I want to have a format for my cells that basically means "the data in this cell specifies a range of integers". For example, it might say 1-4, or 2-7, etc. Or it might just say 1 which is the same as 1-1 (low val is 1 and high val is 1). Is there a way I can have a format code for this and use a custom format? Later on in the worksheet, I want to be able to extract the min and max values of this range from the cells. The application of this is that a certain column is going to be used for holding ranges of numbers. Then at the very bottom I want to add up the ranges to produce...

Conditional Formatting (How to format a range depending on another range)
I'd like to format Columns C, D, E, F, and G based on whether their cell content is greater than the cell in the same row in column B. It seems that the conditional format is always based on a single cell, not a range like this. Can anyone explain to me how to accomplish this? Thanks so much for your time and knowledge, Josh Just make sure you "anchor" your formula. I selected C2:G99 and with C2 the activecell I applied format|conditional formatting I used a formula of: =c2>$b2 The $b tells excel to always look at column B. No $ in front of the 2 means that excel wil...

Sample Range of Data
I have created a sample using data analysis but I want the sample to include all of the columns, not just the sample #. For example if I use data analysis to select a random sample from a worksheet it will return just the sample area I selected. It appears I can only sample on numeric values so I have and the numbers 1, 2, 3....in the first column. What I would like to see is when it returns the sample that it returns all columns not just the first one. Hope I made sense. Jamie ...

Select range in VBA through variabel
Hi I try this code, but I will not work. sub selectnamedrange(fblock as Integer) dim frow_string frow_string = "f" & fblock & "_frow" Range(frow_string).Select ' Which is translated into Range("f1_frow").Select ' f1_frow is a named range in the current worksheet..... ' The sub contains more code, but this is the part that does not work.... end sub. Any hints appreciated Mr. Smith Works fine for me. Are you sure that fblock is 1? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "...

Between/ range function
How could I write a function that would return a number in one cell, if a number in another cell falls between a certain range? (if A1 is between C1:D1 then E1, else "0") (if .23 is between .039:.002 then 15, else "0") =IF(AND(A1>=C1,A1<=D1),E1,0) and =IF(AND(A1>=.002,A1<=.039),E1,0) -- HTH RP (remove nothere from the email address if mailing direct) "sanpanico" <sanpanico@discussions.microsoft.com> wrote in message news:F5A58078-1AE7-43B1-A773-0C75A386AFF2@microsoft.com... > How could I write a function that would return a number i...

How can I apply the ROUND function to a range of cells in a workbo
I have a large worksheet full of formulas which all need to be rounded to the nearest thousand. Without adding the "ROUND" command to every formula individually (which would take forever) is there a way to apply rounding to a whole range? You can just use formatting to visually round what you are seeing, and if you have other formulas that refer to that whole range, then you can adjust those to round the data that they are getting from that range. Give us some more detail and we can be a bit more specific. -- Regards Ken....................... Microsoft MVP - Ex...

VBA Code to select and format range
Hi All, I want to select a range of cells and format the range of cells based on a "offset" feature... The last couple lines in my code are: ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Where the active cell reference is a moving target depending on the amount of data in the worksheet. How do I now say - select this cell, plus other cells in this range of cells and format them with a border, and a color? I can't get the syntax right. I I record the macro, it only gives me this: range("I32:L37&q...

Defined range difficulty
The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat You obviously have data in C1:C77 thus it will be counted, you can use =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536),1)) Regards Peo Sjoblom "Pat" wrote: > The defined range below extends the ran...

Dynamic ranges and reporting
I cannot get my dynamic ranges to work. I've followed several articles but can't get it to work for the row/column combination I have going. A B C D E F 1 startmo Feb-08 2 Noofmonths 3 3 4 Jan-08 Feb-08 Mar-08 Apr-08 May-08 5 2 4 6 9 11 Desired end result 6 MAR-08 Apr-08 May-08 7 6 9 11 Row 4 is a named range called "allmonths" I'd like to have formula...

SUMIF/AVERAGEIF with mulitle range and sum ranges
Im getting an #VALUE! when putting in this formula =AVERAGEIF(($B$6:$B$48,$B$70:$B$112,$B$134:$B$176,$B$198:$B$240),B262,($C$6:$C$48,$C$70:$C$112,$C$134:$C$176,$C$198:$C$240)) I know I'm using it wrong, please help! You can't use muliple range references like that with AVERAGEIF. Kind of long (but not as long as it could get!): =SUMPRODUCT(--(CHOOSE({1,2,3,4},B6:B48,B70:B112,B134:B176,B198:B240)=B262),CHOOSE({1,2,3,4},C6:C48,C70:C112,C134:C176,C198:C240))/SUMPRODUCT(--(CHOOSE({1,2,3,4},B6:B48,B70:B112,B134:B176,B198:B240)=B262)) -- Biff Microsoft Excel MVP &qu...

date range in a query 02-07-08
hello i have the following code in a query [forms]![FORM2]!Between [datein] And [datein2] i am tryng to perform a date range find using the two fields on form2 [datein] and [datein2] but to no avail any ideas! -- Thanks Steve Try this --- Between [forms]![FORM2]![datein] And [forms]![FORM2]![datein2] -- KARL DEWEY Build a little - Test a little "Steve" wrote: > hello i have the following code in a query > [forms]![FORM2]!Between [datein] And [datein2] > > i am tryng to perform a date range find using the two fields on form2 > [datein] and [datein2] but to...

Set Bookmark Range results in Type Mismatch
I'm writing a VBA program which copies information from Excel into bookmarks in Word. I originally wrote it to be run from the Word document, but it makes more sense to have it in Excel so I'm migrating it over. The original code worked, and used ActiveDocument throughout, which I've replaced with myDoc. However, when run it gives the Type Mismatch error when I try to set the bookmark range and I can't figure out why. myDoc is defined and works well in other places (for instance, if you add the line: msgbox myDoc.Bookmarks(BookmarkToUse) into this sub it corre...