Need help merging two Worksheet_Change modules #3

OK, I have managed to get this far.  It all works other than after th
messaage appears and it selects the cell again, you can click away fro
the cell -

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
'Does the validation range still have validation?
If HasValidation(Me.Range("ValidationRange")) = False Then
With Application
.EnableEvents = False
.Undo
End With
MsgBox "Your last operation was canceled. " & _
"It would have deleted data validation rules.", vbCritical
Else
If Intersect(Target, Me.Range("ValidationRange")) Is Nothing Then
'do nothing
Else
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
End If

errHandler:
Application.EnableEvents = True
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
Dim X As String
On Error Resume Next
X = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

--------------------------------------
This module works on its own but I can't see what I have missed from i
in my module above -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$6" And [A6].Value = "Invalid" Then
MsgBox "You have an invalid entry in cell A6"
[A6].Select
SendKeys "%{Down}"
End If
End Su

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

0
9/28/2004 10:18:20 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
423 Views

Similar Articles

[PageSpeed] 12

I'm confused about what message appears--what part of the code is causing the
error.

And I'm confused about what happened to A6 in the worksheet_change event?



fuzzyfreak wrote:
> 
> OK, I have managed to get this far.  It all works other than after the
> messaage appears and it selects the cell again, you can click away from
> the cell -
> 
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> On Error GoTo errHandler:
> 'Does the validation range still have validation?
> If HasValidation(Me.Range("ValidationRange")) = False Then
> With Application
> EnableEvents = False
> Undo
> End With
> MsgBox "Your last operation was canceled. " & _
> "It would have deleted data validation rules.", vbCritical
> Else
> If Intersect(Target, Me.Range("ValidationRange")) Is Nothing Then
> 'do nothing
> Else
> 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
> End If
> 
> errHandler:
> Application.EnableEvents = True
> End Sub
> 
> Private Function HasValidation(r) As Boolean
> ' Returns True if every cell in Range r uses Data Validation
> Dim X As String
> On Error Resume Next
> X = r.Validation.Type
> If Err.Number = 0 Then HasValidation = True Else HasValidation = False
> End Function
> 
> --------------------------------------
> This module works on its own but I can't see what I have missed from it
> in my module above -
> 
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Address <> "$A$6" And [A6].Value = "Invalid" Then
> MsgBox "You have an invalid entry in cell A6"
> [A6].Select
> SendKeys "%{Down}"
> End If
> End Sub
> 
> --
> fuzzyfreak
> ------------------------------------------------------------------------
> fuzzyfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14655
> View this thread: http://www.excelforum.com/showthread.php?threadid=263857

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/28/2004 8:10:49 PM
Reply:

Similar Artilces:

Pls Help!!
Hello i need to check a row for data and if that data is their i need to the check the next row and if the data isnt their then i need to put th data in that range i know how to add the data using VBA but i dont know how to check tha row for the data and if it does exist i need to goto the next row Hope this helps, Jami -- Message posted from http://www.ExcelForum.com for each c in selection if c="mydata" then c="mydata" next c -- Don Guillett SalesAid Software donaldb@281.com "boris2004 >" <<boris2004.12e7s7@excelforum-nospam.com> wrote in me...

.NET 3.0, 3.5 SP1, Media Center Issues
Well, I managed to download .NET Framework 3.0, in lieu of 3.5 simply DISCONNECTING non-stop, and though it (3.0) tells me that it has been successfully downloaded and you can now disconnect from the internet", "setup is proceeding to install it" etc., and not more than a few seconds elapse before it tells me that "an error occured, all files are being removed from the computer"...an icon is on the desktop, and I'm advised that when I click on the install icon it will be installed...again, and again..ad nauseam. 3.5 SP-1 will not even complete downlo...

duplicates #3
Dear People, Does anyone know how to delete duplicate entries from excel? With conditional formatting it seems ezy enuf to find dupliates but how to delete them ... apart from one at a time that is. Ok when the list is short but when its long as your arm, that can seriously cut into ones drinking time. John One way .. Take the sample data below assumed in A2:A5: > Mark Davies > John Smith > David Jones > Mark Davies > David Jones > John Smith Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,1,"") Copy down to B5 This'll mark any duplicates in col A with a &quo...

Newsgroups in Outlook 2003 #3
I know this sounds stupid, but I cannot find out how to get my newsgroups integrated with Office 2003 Can anyone send me the directions [ eg. files ->...]? Thanks //Andreas You cannot. Newsgroups require Outlook Express to read. "Andreas" <andreas_skjaemt@privat.tele.dk> wrote in message news:BD4EFDCB-2B33-47F1-A91C-F9BDAD8231D0@microsoft.com... > I know this sounds stupid, but I cannot find out how to get my newsgroups integrated with Office 2003. > > Can anyone send me the directions [ eg. files ->...]?? > > Thanks! > > //Andreas Is that a n...

mailbox size #3
hi, I want to decrease the size of our users mailboxes to decrease their limits. To do this, I need to justify that any mailbox size over 500mb needs to be revised because ...(good reason). Does anyone have a MSFT document that explain something like that? Thanks I dont think you will find anything like that. What is your SLA in case of disaster and you need to do a full restore? On Fri, 04 Jun 2004 18:20:51 -0200, Marcelo Moraes <marcelo.moraes@noemail.com> wrote: >hi, >I want to decrease the size of our users mailboxes to decrease their limits. To do this, I need to just...

Do we need to install active sync?
Hi all, We have Exchange 2003 Sp2 frontend and backedn servers. I enabled the mobile function in the global seetings. BTW, do we still need to install active sync in the server ot workstaions? Thanks. In news:CD56A612-B0ED-47F0-9428-0B2584DE3AB4@microsoft.com, Sally <Sally@discussions.microsoft.com> typed: > Hi all, > > We have Exchange 2003 Sp2 frontend and backedn servers. I enabled the > mobile function in the global seetings. BTW, do we still need to > install active sync in the server ot workstaions? > > Thanks. You don't install ActiveSync deskt...

Question Variation: Shading a portion of the space between two lines
I posted a question on March 21 about how to shade a small portion of space between two lines and got an answer from Jon that helped immensely. I have a slight variation on that question. I've tried manipulating the data in several ways myself, and got close, but no cigar. Here is my revised question. I have another chart with two intersecting lines. I want to shade a small portion of the space before the intersection and another small portion of the space after the intersection. Specifically, there are 13 points on each line and I want to shade the space between points 1 and 5 and a...

HELP: Handling WM_SHOWWINDOW when window opened SW_SHOWMAXIMIZED
I've verified that the WM_SHOWWINDOW handler OnShowWindow() is not called when ShowWindow is called with SW_SHOWMAXIMIZED. I created a dummy MFC app (doc/view and no doc/view) and reproduced the behavior. So, here is what I have (simplified)... MFC app with no doc/view support. CMyWinApp::InitInstance() { .... pMainFrame->ShowWindow(m_nCmdShow); pMainFrame->UpdateWindow(); .... } CMainFrame::OnShowWindow(...) { // Not called when m_nCmdShow == SW_SHOWMAXIMIZED!! } This seems to be the designed behavior. Does anyone know why? What message can I handle in this case to know when the...

Share Workbooks and Comments #3
Can anyone help me with this one... I have a simple spreadsheet which is shared and resides on a server. Apprx 6 people access and modify the sheet and save their modifications back to the server. No 2 people ever change the same cell so there are never any conflicts. The problem I have is that often the comments in a cell do not upload to the server. The user is unaware of this util he closes and re-opens the workbook to find that all of his changes have been saved but none of his comments. I have been getting the users to make their own copy at the end of the day and if they e-mail that c...

Number format #3
Ah, works perfectly. Thanks -- carg ----------------------------------------------------------------------- carg1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1527 View this thread: http://www.excelforum.com/showthread.php?threadid=27578 ...

IF statement help #2
Hi What im trying to do is, Calculate a Cell x say 0.5. If the result is a negative number, sho 0. If it is a positive number, work it out and show the answer. Jus tried, and cant seem to get it right for some reason. I dont want t have to use another cell for no reaon, as I need to present th worksheet This is what I tried, IF((E22*0.5)>0), (E22*0.5), Thank -- RudeYut ----------------------------------------------------------------------- RudeYute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3295 View this thread: http://www.excelforum.com/showthr...

Need to backup Contacts
I am running Outlook 2000 behind Windows ME. I want to make a backup of Contacts but cannot find the location of the file. When I search on *.pab, it returns "not found". I have over 850 entries in Contacts, so I know the file exists somewhere. Any suggestions on where to find or how to back-up? Thanks. Export your contacts folder to a .pst file. The .pab is old and obsolete and has not been used for a number of releases. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all ...

help attaching files
Hi... Using XP Pro and Outlook 2003. For some odd reason, I am unable to attach files when sending emails. Click on the 'paperclip', window opens to the My Doc folder. then when I click on a sub folder to open, nothing happens... finally, a Microsoft Word window opens that it is unable to open.... It is almost as if it is trying to launch Word to open the folder... Help? Thanks! -- B'rgds, Vinnie FWIW, from the Microsoft side a new Knowledge Base Article (KBA) was released on Apr 15th. http://support.microsoft.com/?kbid=918165 It lists two known conflicts so far....

Help, urgent, connection to access database
Hi, all I met a problem about access database connection. I have a program reading data from access database through ODBC. When the access database located on local server, and created ODBC linked to local database,the program runs without problem, but if it is a network shared database, and created ODBC linked to the shared database, the program prompts error: "workgroup file is missing". Yes, I used workgroup file in current database and set correct security. So what's the problem? is problem of the ODBC drive? you should not be using ODBC Access stop...

Cell Reference #3
In a workbook, is it possible to know all the cells where a particular cell is referenced in a calculation. Specifically, if I make a change in one cell, what are all the cells that will be affected. ...

Number to text conversion ......HELP
Dear Friend.. I need help in converting formula calculation ( Number or Currency ) into Text EQ. Result = 2,500.00 converting to .....two thousand five hundred.. Thanks Check http://www.mvps.org/access/modules/mdl0001.htm at "The Access Web" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Eko Hery" <eko@megaenterprise.com> wrote in message news:BCD8B0CE.1D8%eko@megaenterprise.com... > Dear Friend.. > I need help in converting formula calculation ( Number or Currency ) into > Text > > EQ. Result = 2,500.00 con...

AOL to Outlook 2003, transfer contacts and favorites, help?
Moving from 7 years of AOL service to a new ISP and using Outlook 2003 now. How do I avoid having to copy/paste hundreds of contacts one at a time from my AOL address book to Outlook. Also have lots of 'favorites' on AOL I would like to keep... Jakfrost "Jakfrost" <Jakfrost@discussions.microsoft.com> wrote in message news:924061C2-70C6-45DE-BA7D-9533902A9783@microsoft.com... > Moving from 7 years of AOL service to a new ISP and using Outlook 2003 > now. > How do I avoid having to copy/paste hundreds of contacts one at a time > from > my AOL addres...

merge cells with a twist
A B C joe @ domain.com trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and that result is joe@domain.com Thank you. =A1&B1&C1 -- Regards, Peo Sjoblom (No private emails please) "jd" <jdumont@@novuscom.net> wrote in message news:uaAWelbgFHA.3436@tk2msftngp13.phx.gbl... >A B C > joe @ domain.com > > trying to merge cells in a row so that column a(joe) b(@) c(domain.com) > and > that result is joe@domain.com > Thank you. > > =hyper...

Compare two Sheets with Conditional Formatting #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Argent Help
Hello All Can Any One Tell Me How Can I Get The Number Of Visible Sections In A Report To Use As The Upper Bound Of A Loop Of Sections Because With A Worng Number Of Sections An Error Occured So If There Is Any Way (Using API,...) Any Way Please Tell Me Thanks And Happy New Year For All Rabea Khalil "Developer" <shamijewelerjo@yahoo.com> wrote > Hello All > Can Any One Tell Me How Can I Get The Number Of Visible Sections In A > Report > To Use As The Upper Bound Of A Loop Of Sections Because With A Worng > Number Of Sections An Error Occured > > S...

Investors Needed!!! #5
INVESTMENT OPPORTUNITY! Fork It Over! a Ground Floor Unique Dimension & Concept In the Children's Entertainment Market... For More information http://www.kidseyeview.us ...

"Message Tracking Center" showing incorrect time #3
I have applied all of the DST patches for the OS and Exchange Server. OS is Server 2003 Standard (latest patches) and Exchange 2003 Standard (latest patches). I noticed today that when I go into the "message tracking center" teh defult end log time is one hour ahead of the computer time. Also, all of the sent times being tracked are showing as being sen one hour in the future. (i.e. real time is 3:00 PM, messages in tarckign center show having been sent at 4:00 PM). Any ideas? Thanks, Bob I don't have an answer, but I'm seeing the same thing on my system. "Bob...

Another CPropertySheet help...
I am using the Wizard mode and I can remove the buttons, but I also want to remove the 3D line control that appears above the buttons. What is the control ID? Thanks!... ...

It's twins!!! Two sets of personal folders!!
I had to re-install outlook and reloaded my .pst file. My inbox got all the mail back but my contacts didnt come back. I imported the pst file and now i have double!! It wont let me get rid of either. What do I do now?? TIA Terri ...

Excel and "AutoRun.inf" -- help
Greetings list: I want to burn an Excel workbook to a CD. When the CD is inserted into a computer, I would like it to automatically start up Excel and OPEN my workbook. I've tried some "helper" applications for getting the autorun.inf file to open Excel [and my workbook] when the CD is inserted into the comptuter. It's not working! Can anyone help? Penny I think you might have more success in a site that deals wit Powerpoint -- Message posted from http://www.ExcelForum.com ...