 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / June 2006Message box to pop up if a particular value is entered
Hi! I was wondering if it is possible to write a macro so that if any of the words "admin", "manager" or "co-ordinator" are entered into any of the cells A1:B3, then a message box will pop up saying "Your current user status allows editing". It would be great if it would work if any of those three words are put in, but if it can only look out for one particular word, "admin" would be enough. Ideally (and this really would be perfect!) it would be great if the macro could still look at cells A1:B3, but that it would have different messages depending on which key-word is entered. e.g. - If "admin" is entered it would display "You may edit data only" - If "manager" is entered it would display "You have editing data and modify content" - If "co-ordinator" is entered it would display "Please contact extension 1453 for more information" Can anybody think of a way of doing this? (I am a bit of a Visual Basic novice, but can follow instructions!) Many thanks in advance, Neil Goldwasser Try placing the following code in the "This workbook" workbook.sheetchange event. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) For Each c In Range("A1:B3") Select Case c Case "admin" MsgBox (c) Case "manager" MsgBox (c) Case "co-ordinator" MsgBox (c) End Select Next c End Sub replace c with what ever message you want to display. Jim F > Hi! I was wondering if it is possible to write a macro so that if any of the > words "admin", "manager" or "co-ordinator" are entered into any of the cells [quoted text clipped - 15 lines] > > Many thanks in advance, Neil Goldwasser Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then Select Case Target.Value Case "admin": MsgBox "You have editing data and modify content" Case "manager": MsgBox "You have editing data and modify content" Case "co-ordinator": MsgBox "Please contact extension 1453 for more information" End Select End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in.  SignatureHTH RP (remove nothere from the email address if mailing direct) > Hi! I was wondering if it is possible to write a macro so that if any of the > words "admin", "manager" or "co-ordinator" are entered into any of the cells [quoted text clipped - 15 lines] > > Many thanks in advance, Neil Goldwasser Assumes A1:B3 does not have merged cells. Private Sub Worksheet_Change(ByVal Target As Range) Dim msg as String If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:B3")) Is Nothing Then msg = "" Select Case LCase(Target) Case "admin" msg = "You may edit data only" Case "manager" msg = "You have editing data and modify content" Case "co-ordinator" msg = "Please contact extension 1453 for more information" End Select If msg <> "" Then MsgBox msg End If End If End Sub Right click on the sheet tab and select view code. Paste in code similar to the above. SignatureRegards, Tom Ogilvy > Hi! I was wondering if it is possible to write a macro so that if any of the > words "admin", "manager" or "co-ordinator" are entered into any of the cells [quoted text clipped - 15 lines] > > Many thanks in advance, Neil Goldwasser Many, many thanks to everyone that has helped me with this - you don't know how much I appreciate it! I have one more novice question though, if I may ask. I had already inserted a Worksheet_Change code for that worksheet, as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rTarget As Range Dim rCell As Range Set rTarget = Intersect(Target, Range("Supported_By_2")) If Not rTarget Is Nothing Then For Each rCell In rTarget If rCell.Value = "** N/A **" Then Application.EnableEvents = False rCell.Offset(0, 1).Value = "N/A" MsgBox "Cell " & rCell.Offset(0, 1).Address(False, False) & " has automatically changed to N/A because a " & "second support plan was not selected" Application.EnableEvents = True End If Next rCell End If End Sub (And again, many thanks to JE McGimpsey, sandved@gmail.com and Bob Phillips for helping me construct that one!) The problem is that when I added a second worksheet change macro, I received an error message saying "Compile error: Ambiguos name detected: Worksheet_Change" I'm sure it is a question that only a novice like myself could ask, but how can I have more than one worksheet_change macro running in the same worksheet (though functioning on different cells)? Ideally, both of these functions are useful. I am hoping it is a case of just renaming the codes somehow to have two different names, but when I had a go they both stopped working. Would somebody please be able to advise me on how to alter the code to have more than one macro running. Then if I am brave I might even try adding a third bit later on to see if I've learned it - and won't need to pester everyone :-) Many thanks, Neil Goldwasser > Assumes A1:B3 does not have merged cells. > [quoted text clipped - 45 lines] > > > > Many thanks in advance, Neil Goldwasser Hi Neil, It is only possible to have one Worksheet_Change procedure behind a given worksheet. However, it is possible to include alternative operations acting on different ranges within the one procedure. So, the solution to your problem is to combine the code from each of your two versions into one single procedure. --- Regards, Norman > Many, many thanks to everyone that has helped me with this - you don't > know [quoted text clipped - 104 lines] >> > >> > Many thanks in advance, Neil Goldwasser Hi Norman! Thanks for the explanation. I have now sandwiched both sets of code between the "Sub Worksheet_Change(ByVal Target As Excel.Range)" line and the "End Sub" line and it is working really well. Sorry if it was a really simple question, but I am a novice, and if I didn't ask I would never know. I’m learning more all the time now though. My racing mind has now thought up a new question though. At the moment, one of my sections of code is: On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then Select Case Target.Value Case "admin": MsgBox "You may edit data only" Case "manager": MsgBox "You may edit data and modify content" Case "co-ordinator": MsgBox "Please contact Neil on extension 1453 for more information" End Select End If ws_exit: Application.EnableEvents = True This allows three different messages for each of the three different "roles". If new roles, or new people came into the team, is there a quick way of adapting the code so that, for example, the message "Please contact extension 1453 for more information" would show for more than one person? I realise that I could keep adding cases, e.g. Case "admin": MsgBox "You have edit data only" Case "manager": MsgBox "You may edit data and modify content" Case "co-ordinator": MsgBox "Please contact Neil on extension 1453 for more information" Case "IT team" MsgBox "Please contact Neil on extension 1453 for more information" and so on... but is there a quicker way, something along the lines of Case "co-ordinator" "IT team": MsgBox "Please contact extension 1453 for more information" Because this way is quicker I could include the different variations, to ensure that each role is picked up, e.g. co-ordinator, Co-ordinator, coordinator, Coordinator, and so on, in case they spelled it differently by accident. Again, thanks to all that have helped with my education, Neil Goldwasser > Hi Neil, > [quoted text clipped - 118 lines] > >> > > >> > Many thanks in advance, Neil Goldwasser Hi Neil, > but is there a quicker way, something along the lines of > Case "co-ordinator" "IT team": > MsgBox "Please contact extension 1453 for more information" Indeed there is. and you are only a whisker (or a comma!) away. Simply separate the case items with a comma: Case "Manager1", " Manager2", "Mamager3", ..."Manager20" MsgBox "Please contact extension 1453 for more information" --- Regards, Norman > Hi Norman! Thanks for the explanation. I have now sandwiched both sets of > code between the [quoted text clipped - 191 lines] >> >> > >> >> > Many thanks in advance, Neil Goldwasser Thanks Norman, I knew it would be something embarrassing! I'd tried everything else, but not that one. Cheers. Would there also be a way of naming cells, so that I could group together all the users who, for example, I will allow modification rights, as one name, e.g. "ModificationRights"? I tried the following, but it is looking for "ModificationRights" as the value, and I don't know how to alter the code to allow the range of values that come under the name "ModificationRights" instead: On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then Select Case Target.Value Case "admin": MsgBox "You may edit data only" Case "ModificationRights": MsgBox "You may edit data and modify content" Case "co-ordinator": MsgBox "Please contact Neil on extension 1453 for more information" End Select End If ws_exit: Application.EnableEvents = True Any chance that you might be able to solve this one as well please? Thankyou, Neil Goldwasser > Hi Neil, > [quoted text clipped - 208 lines] > >> >> > > >> >> > Many thanks in advance, Neil Goldwasser Neil, Do you mean? On Error GoTo ws_exit: Application.EnableEvents = False Select Case True Select Case Target.Value Case Not Intersect(Target, Me.Range("admin"")) Is Nothing Then MsgBox "You may edit data only" Case Not Intersect(Target, Me.Range("ModificationRights")) Is Nothing Then MsgBox "You may edit data and modify content" Case Not Intersect(Target, Me.Range("co-ordinator")) Is Nothing Then MsgBox "Please contact Neil on extension 1453 for more information" End Select ws_exit: Application.EnableEvents = True End Sub SignatureHTH RP (remove nothere from the email address if mailing direct) > Thanks Norman, I knew it would be something embarrassing! I'd tried > everything else, but not that one. Cheers. [quoted text clipped - 240 lines] > > >> >> > > > >> >> > Many thanks in advance, Neil Goldwasser Hi Neil, Or did you mean something like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then Select Case Target.Value Case "admin": MsgBox "You may edit data only" Case "ModificationRights": MsgBox "You may edit data and modify content" Case "co-ordinator": MsgBox "Please contact Neil on extension " & _ "1453 for more Information " Case Else If Not IsError(Application.Match(Target.Value, _ Range("ModificationRights"), 0)) Then MsgBox "Your Group Message!" End If End Select End If ws_exit: Application.EnableEvents = True End Sub --- Regards, Norman > Thanks Norman, I knew it would be something embarrassing! I'd tried > everything else, but not that one. Cheers. [quoted text clipped - 258 lines] >> >> >> > >> >> >> > Many thanks in advance, Neil Goldwasser Hi again! Thankyou both for your suggestions. Unfortunately though I cannot get either method to work. I grouped together a list of usernames under the name "ModificationRights", one of those being "ngoldwasser". However, when I typed "ngoldwasser" into one of the cells A1:B3, it didn't work as planned. I'm sure it is something I am doing, but here is what happens: With Bob's method I receive a syntax error, and the "select case..." and "case not intersect..." lines appear in red. I put this code in the worksheet_change event. With Norman's method nothing happens if I type in ngoldwasser. I tried typing in "admin" as well, and no message box popped up until I clicked bak on the cell a second time. I had put this code in as a separate worksheet_selection change event. Can anyone tell me what I'm doing wrong please? Many thanks, Neil Goldwasser > Hi Neil, > [quoted text clipped - 262 lines] > >> >> >> > A1:B3, then a message box will pop up saying "Your current user > >> >> >> > status Hi Neil, I inadvertently used the Worksheet_SelectionChange event. Change this to: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then Select Case Target.Value Case "admin": MsgBox "You may edit data only" Case "ModificationRights": MsgBox "You may edit data and modify content" Case "co-ordinator": MsgBox "Please contact Neil on extension " & _ "1453 for more Information " Case Else If Not IsError(Application.Match(Target.Value, _ Range("ModificationRights"), 0)) Then MsgBox "Your Group Message!" End If End Select End If ws_exit: Application.EnableEvents = True End Sub If I then defined (via Insert | Names) a range (say, J:10) as ModificationRights, then typing any entry from the ModificationRights range brought up the message: "Your Group Message!" . My sub was intended purely for demo purposes and it is unlikely that you would also want additionally to test for the "ModificationRights" string (see the 2nd Case) --- Regards, Norman > Hi again! Thankyou both for your suggestions. Unfortunately though I > cannot [quoted text clipped - 313 lines] >> >> >> >> > user >> >> >> >> > status Thanks Norman! It's working now! One quick question though - I have discovered that it will only work for me if that set of named cells is on the same sheet, i.e. the values that come under the "ModificationRights" name are also on the sheet with the macro in it. I had previously been putting all the named cells that I will use in the drop-down lists etc... in a different sheet, to keep things "tidy". Is there a way that it will let me hide the names in a different sheet? I was also wondering if there is a simple way of adding to the code so that I could have several groups, each with their own message. E.g. "ModificationRights" would show "You may edit data and modify content" "DataEditingRights" would show "You may edit data only" "ReadOnlyRights" would show "You only have read-only access and may not edit data" Sorry if I'm being a pain, but this will pay off big time in the future! Many thanks, Neil Goldwasser > Hi Neil, > [quoted text clipped - 263 lines] > >> >> >> > rCell.Offset(0, 1).Value = "N/A" > >> >> >> > MsgBox "Cell " & rCell.Offset(0, Hi Neil, I put the code in the module behind Sheet1; I selected Sheet2 and defined the name ModificationRights to refer to cells A1:A10 (Insert | Name | Define ...); I populated the newly named range on Sheet2 with some first name (Anne, Ben, Carol...). Returning to Sheet1, entering any of the names from the Sheet2 named range caused a "Your Group Message!" msgbox to appear. Assuming that the name is a workbook level name, it should not matter where the named range is within the workbook. --- Regards, Norman > Thanks Norman! It's working now! > [quoted text clipped - 309 lines] >> >> >> >> > rCell.Offset(0, 1).Value = "N/A" >> >> >> >> > MsgBox "Cell " & rCell.Offset(0, > Hi Neil, > [quoted text clipped - 263 lines] > >> >> >> > but is there a quicker way, something along the lines of > >> >> >> > Case "co-ordinator" "IT team": Can you change this to happen when any cell is selected instead of typing admin or anything else into a cell? Hi Neil, > I was also wondering if there is a simple way of adding to the code so > that [quoted text clipped - 7 lines] > > Sorry if I'm being a pain, but this will pay off big time in the future Try: '=============================>> Private Sub Worksheet_Change(ByVal Target As Range) Dim arr As Variant Dim ArrMsg As Variant Dim i As Long Dim NamesSheet As Worksheet Set NamesSheet = Sheets("Sheet2") '<<==== Change ArrMsg = Array("You may edit data only", _ "Check Your modifcation rights!", _ "You only have read-only access and may not edit") arr = Array("DataEditingRights", _ "ModificationRights", _ "ReadOnlyRights") On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:B3")) Is Nothing Then For i = LBound(arr) To UBound(arr) If Not IsError(Application.Match(Target.Value, _ Sheets(2).Range(arr(i)), 0)) Then MsgBox ArrMsg(i) Exit For End If Next End If ws_exit: Application.EnableEvents = True End Sub ''<<'============================= --- Regards, Norman > Thanks Norman! It's working now! > [quoted text clipped - 309 lines] >> >> >> >> > rCell.Offset(0, 1).Value = "N/A" >> >> >> >> > MsgBox "Cell " & rCell.Offset(0, Hi Neil, Typo warning! Change: > Sheets(2).Range(arr(i)), 0)) Then to: > NamesSheet.Range(arr(i)), 0)) Then --- Regards, Norman > Hi Neil, > [quoted text clipped - 374 lines] >>> >> >> >> > rCell.Offset(0, 1).Value = "N/A" >>> >> >> >> > MsgBox "Cell " & rCell.Offset(0, Hi Neil, Just to add, each of the named ranges corresponding to the name strings in arr, need to exist. Simply define them via the Insert | Names menu prior to running the code. The sequence of message strings in ArrMsg must match the sequence of named ranges, else the wrong message will appear! --- Regards, Norman > Hi Neil, > [quoted text clipped - 374 lines] >>> >> >> >> > rCell.Offset(0, 1).Value = "N/A" >>> >> >> >> > MsgBox "Cell " & rCell.Offset(0, Hi again Norman! I really appreciate all of this. Unfortunately though I just don't seem to be able to get it right. No doubt it'll be another one of my embarassingly silly mistakes, but it seems as though Excel has got me beaten. I don't suppose you would have made a "tester" workbook that you could e-mail me instead do you. Don't worry if not, or if sending files isn't a done thing in discussion groups (I'm relatively new to the whole community thing and I'm still not sure of the right etiquette!), but if you could that would be amazing. My e-mail address if you can do it is ngoldwasser@hrc.ac.uk (Hertford Regional College being the base of the learning support department that is currently benfiting from all your hard work!) Either way, many thanks for all your help. I'll keep trying! Neil Goldwasser > Hi Neil, > [quoted text clipped - 260 lines] > >>> >> >> "Neil Goldwasser" <NeilGoldwasser@discussions.microsoft.com> wrote > >>> >> >> in Hi Neil, I Have sent a simple demo book. For future reference, you would be well advised to disguise your email address: spammers voraciously pounce on any unwarily published email addresses. Looking at the posts of regular contributors, you will see examples of such disguise. My own paranoia is such that I only post a (disguised) address if explicitly inviting an email. BTW as I am from St Albans, you are a mere stone's throw away! --- Regards, Norman > Hi again Norman! I really appreciate all of this. Unfortunately though I > just [quoted text clipped - 303 lines] >> >>> >> >> wrote >> >>> >> >> in It works! It works! It works! Thanks ever so much for that Norman, you've made my day! I was even brave enough to try adding a fourth group and corresponding message to see if I was able to add subsequent groups, and to my surprise I could! Just goes to show what you can do when you have somebody much cleverer to do it for you!! :-) I owe you one, this will really help our new tracking system! (in case I haven't already mentioned the overall aim of my project is to design a brand new tracking form to effectively monitor the support provided to the students we have at the college with learning difficulties. It will now mean that students won't "slip through the net" and miss out on the support that they are entitled to. So you have been helping a good cause and I really appreciate it!) Thanks for the warning about spammers by the way, I'll bear that in mind. Okay, I think case closed on that matter now. Thanks to everyone, Neil Goldwasser > Hi Neil, > [quoted text clipped - 260 lines] > >> >>> >> > Would there also be a way of naming cells, so that I could group > >> >>> >> > together |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.