Code does not work on merged cells

The following code works fine on single cells but I also have Dat
Validation which for cosmetic purposes merges to the next cell e.g
Cell B3 merges into cell C3 - why, if I use either B3 or B3:C3 in th
code below does it not work?  i.e. I should get an error message if 
try to delete data from one of the cells.

Thanks

_Code_
Application.EnableEvents = False

If (Len(Range("b10")) = 0) Or (Len(Range("c10")) = 0) O
(Len(Range("f10")) = 0) Then
With Target
If .Value = "" Then
Application.EnableEvents = False
.Value = "Invalid"
MsgBox "You have an invalid entry, please try again."
.Select
SendKeys "%{Down}"
End If
End With
End If
Application.EnableEvents = Tru

--
fuzzyfrea
-----------------------------------------------------------------------
fuzzyfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465
View this thread: http://www.excelforum.com/showthread.php?threadid=27287

0
10/27/2004 4:29:52 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
646 Views

Similar Articles

[PageSpeed] 41

I don't understand the connection between Data/Validation and merged cells...

IMO, you should avoid merged cells like the plague. You've just pointed out
another of the many reasons for that opinion. 

If all of these merges are a cell on the left with one or more cells to its
immediate right (i.e. in the same row), and the point is to center some text
across, for example, B3:C3, you can achieve that result by selecting both
cells, then setting the horizontal alignment to "center across selection".


On Wed, 27 Oct 2004 11:29:52 -0500, fuzzyfreak
<fuzzyfreak.1esmvy@excelforum-nospam.com> wrote:

>
>The following code works fine on single cells but I also have Data
>Validation which for cosmetic purposes merges to the next cell e.g.
>Cell B3 merges into cell C3 - why, if I use either B3 or B3:C3 in the
>code below does it not work?  i.e. I should get an error message if I
>try to delete data from one of the cells.
>
>Thanks
>
>_Code_
>Application.EnableEvents = False
>
>If (Len(Range("b10")) = 0) Or (Len(Range("c10")) = 0) Or
>(Len(Range("f10")) = 0) Then
>With Target
>If .Value = "" Then
>Application.EnableEvents = False
>Value = "Invalid"
>MsgBox "You have an invalid entry, please try again."
>Select
>SendKeys "%{Down}"
>End If
>End With
>End If
>Application.EnableEvents = True

0
anonymous (74722)
10/27/2004 6:05:15 PM
I imagine the point of having merged cells is to allow a wider selection box
for the DV list.

A good idea until you try to manipulate the merged cells.

Check out Help on "MergeCells Property"

May be an example there that could assist with code-writing.


Gord Dibben Excel MVP

On Wed, 27 Oct 2004 13:05:15 -0500, Myrna Larson
<anonymous@discussions.microsoft.com> wrote:

>I don't understand the connection between Data/Validation and merged cells...
>
>IMO, you should avoid merged cells like the plague. You've just pointed out
>another of the many reasons for that opinion. 
>
>If all of these merges are a cell on the left with one or more cells to its
>immediate right (i.e. in the same row), and the point is to center some text
>across, for example, B3:C3, you can achieve that result by selecting both
>cells, then setting the horizontal alignment to "center across selection".
>
>
>On Wed, 27 Oct 2004 11:29:52 -0500, fuzzyfreak
><fuzzyfreak.1esmvy@excelforum-nospam.com> wrote:
>
>>
>>The following code works fine on single cells but I also have Data
>>Validation which for cosmetic purposes merges to the next cell e.g.
>>Cell B3 merges into cell C3 - why, if I use either B3 or B3:C3 in the
>>code below does it not work?  i.e. I should get an error message if I
>>try to delete data from one of the cells.
>>
>>Thanks
>>
>>_Code_
>>Application.EnableEvents = False
>>
>>If (Len(Range("b10")) = 0) Or (Len(Range("c10")) = 0) Or
>>(Len(Range("f10")) = 0) Then
>>With Target
>>If .Value = "" Then
>>Application.EnableEvents = False
>>Value = "Invalid"
>>MsgBox "You have an invalid entry, please try again."
>>Select
>>SendKeys "%{Down}"
>>End If
>>End With
>>End If
>>Application.EnableEvents = True

0
Gord
10/27/2004 10:02:56 PM
Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

Re: merging a pub file into a pub document?
GbH wrote: > I'm sure I've seen this somewhere in here or a similar newsgroup. > Someone clever has written a script that will import a publisher > document into an existing/open publisher document. > Please can somebody point me at it. > > -- Also why did my post seem to disappear off the server before I could read it? -- -- Wisdom and experience come with age, they say, but I wish I could remember the darn question Both of your posts show -- Don Vancouver, USA up here. "GbH" <Geoff_Hannington@IEE.ORGasm> wrote in message news:u7JmyS0d...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Windows live messenger has stopped working
when I boot my PC and start Windows 7 WLM during the startup phase stops working before it has fully started. If however i start it after the boot up process is complete it works fine. I am using Window 7 64 Home Prem... and am running a new ATI 5800 series card, have 4g of ram etc..... Any ideas? I have read through the posts on this board and i do not see my problem though it is likely related to the other ones i have seen. Greetings, It might be a bit hard to isolate what's causing this without disabling other things starting at startup, as it's probably just a...

OL2002 : "header only" option works only once !!!!!
Hi, I have an IMAP account and OL2002 as client. I have check for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit, download item description only). It works only the first time i start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

Turn off save work reminder
How can I stop being reminded to save my work in Publisher 2000? Thanks in advance, Josh Options, User Assistance tab, clear "Remind to save publication." -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "josh page" <josqb@cinci.rr.com> wrote in message news:46b0a69f$0$8926$4c368faf@roadrunner.com... > How can I stop being reminded to save my work in Publisher 2000? > > Thanks in advance, > Josh > ...

Excel Cell Protection / Locking Problem
Is there a way to protect formulas in a sheet (that is, locking certai cells and protecting the sheet) while still enabling users to add line in unlocked areas of the sheet? I am trying to develop a price quoting form where the user can ad extra lines (if additional line items are added) while I keep th structure of the sheet (formulas that total columns, calculate tax an so forth) intact. I have a feeling that once I protect a sheet, I am foreclosed fro adding line no matter what. Thanks -- Message posted from http://www.ExcelForum.com have you used Data Validation? This restricts wha...

Merge Excel Formatting in Publisher
I am merging into Publisher 2003 from Excel 2003 three separate fields, all identically formatted in Excel as currency, $ symbol, commas where appropriate. Two of the three fields merge with appropriate formatting, e.g. $1,500. The third field merges the figure, but not the formatting, e.g. 1500. Any help greatly appreciated. ...

Counting Cells in a column depending on another column but excluding duplicates
Hello, I would like to count the total in a column depending on another column, but exclude duplicates. I my case I would like to know how many employees (column A) have taken a course in 2010 (column B). Column A has duplicate names. Column B has 2009 and 2010 as the year the course was taken. I want to count 2009 and 2010 separately. I named the range of column A course_attendees and column B course_taken Name Course Year Wilbert Bugay 2009 Wilbert Bugay 2009 Zahid Gul 2009 Zin Minn Lwin 2009 Zin Minn Lwin ...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Maximum Number of Cell Formats
My company uses very large spreadsheets to confrom to goverment requirements. Recently we have run into a Warning message when we try to insert a new column. It says we've used up all the cell formats, so we can't add any new cells. The Microsoft documentation seems to indicate that the maximum number of cell formats is 4000. I guess we're hitting that limit and Excel won't let us enter anymore data. How do I increase the max number of cell formats? How do I find out how many I've used already? How do I delete ones I'm not using (Excel seems to "hang on&quo...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

mail merge not an available option under more actions
Using Outlook SFO on Version 3 CRM. Selecting Microsoft CRM > Workplace > Customers > Accounts in Outlook folder list allows me to have a mail merge option on the more actions drop down menu, but another user (who used to do this on ver 1.2) does not have the option. Any suggstions on how to enable the mail merge option for this user? Thanks, Bernie. Resolved by uninstalling and reinstalling Outlook Client. "Bernie Walker" <bwalker@adcocircuits.com> wrote in message news:uRliUjTQGHA.5560@TK2MSFTNGP10.phx.gbl... > Using Outlook SFO on Version 3 CRM. Sele...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

Dialoog Box Close Buttons Won't Work
Hi: I have a vc++ 6.0 program that I recently converted to an ActiveX control so that I could use it as a part of another program. The program that was converted to an ActiveX control has a number of dialog boxes that pop up to perform various functions. In the original program the dialog boxes work fine, but in the ActiveX control the dialog box won't close when the "Close" button in the upper right corner is pressed. The program gets hung up whenever this button is pressed and I need to do a "ctrl-alt-delete" and an "End Task" to close the program. Ca...

configure outlook to work with AOL
Hello...help How do I configure outlook to work with my AOL? Milt <anonymous@discussions.microsoft.com> wrote: > Hello...help > How do I configure outlook to work with my AOL? You don't. See <http://members.aol.com/adamkb/aol/mailfaq/#3rd-party>. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finger/Web for PGP & S/MIME adamkb@aol.com | http://www.lull.org/adam/ ...

options/tasks OUTLOOK 2007 not working
Example, insert picture option cloeded as if trial period over unable to use many in all office 2007 programs. Was fine for a couple days and then this. Test driving new 2007 version for my office - pretty sure I may have done something customizing. JAS Are you using HTML or RTF message format? Some of the options are only available in specific formats. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a ...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Hard cells
Hi, I have a wookbook, say it is Workbook A, in which the cells take calculations from another workbook, say Workbook B. So A3 in Workbook A may read as 334.40 but it is really a combination of A4 and A6 in Workbook B. Here is my delimma. I want to email Workbook A to someone but email not Workbook B. Is there some way to turn A3 in Workbook A into a hard number, 334.50, so that it will read that number when they receive it? Thanks a lot, Stephen Copy the cell, then choose Edit/Paste Special and select the Values radio button. In article <01d701c39e4b$bfa80020$a301280a@...

count table non empty cells
Hi Layout: WXP SP3, Office 2003. I have a table that has checkmarks in some of it's cells (one character per cell) . I want to use a formula field in the last cell of each column that counts the non empty cells (the ones with checkmarks) from that column. Thanx Crios Exactly what are the checkmarks? Are they FormFields as used in document that is protected for filling in forms? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnew...

Need to check if the value in one cell is correct or not
Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digi...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...