Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 7th, 2011, 04:18 PM
Authorized User
Join Date: Feb 2009
Posts: 25
Thanks: 11
Thanked 0 Times in 0 Posts
Default Limit occurences of text in a range of cells

I am trying to work out an assignment worksheet. This is how it will be set up:
Column A would be a list of employee names.
Column B would be where assignments are placed.
The assignments are: A, B, C, D, E, F, G, H, J, K, L, M, RDO, Ill, Vacation, Comp., Jury
What I want the VBA code to do is limit the assignments A through M to only two occurrences for the list.

Maybe this wouldn't take VBA at all, maybe it could be done with formulas. I don't know.
Thanks in advance for your help.

Old May 1st, 2011, 05:08 PM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts

under the worksheet_change event for that sheet, place a test to see if it's the "assignment" column that has been changed. If so, get the input of the cell that was just changed and scan the column to see if there are already two of those letters. If that is the case, maybe pop up a message box letting the user know there are already two of those assignments. (don't forget to disable events before the code runs)

If you don't know how to do that, repost and I'll work up a piece of sample code :)
Old July 21st, 2011, 09:30 AM
Authorized User
Join Date: Feb 2009
Posts: 25
Thanks: 11
Thanked 0 Times in 0 Posts
Default Limit occurences of text in a range of cells

Back with this again. No, mtranchi, I don't know how to do that. Would you mind working up the sample code?
Thanks in advance

Similar Threads
Thread Thread Starter Forum Replies Last Post
Background color in a range of cells billy1r Excel VBA 9 February 23rd, 2009 11:45 PM
Limit input range millieamp HTML Code Clinic 1 November 8th, 2007 09:40 AM
e.Item.Cells[0].Text Statement problem ACE2084 ASP.NET 1.0 and 1.1 Basics 11 January 28th, 2005 11:08 AM
Wrap text with merged cells aspadda Excel VBA 4 January 29th, 2004 03:02 PM
Searching for Duplicates in A range of Cells smartgir Excel VBA 1 October 23rd, 2003 01:44 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.