I am looking for a VBA event(?) code that would erase another cell if I enter a value in another cell, because the criteria is that only one of the cells are to be allowed a value per row.
ex: cells A1, B1, C1
if I enter 1 in cell A1 and then enter 1 in cell C1, I want cell A1 to erase and cell C1 would have the value 1. I am looking to create these sets for 200 rows.
I am looking for a macro that would accept only one entry per selected ranges. It would be something like if there is one entry in range("b4:e4"), a message box would appear advising the user that only one entry is permitted in that range. It would give the option to delete the entry and to rekey the entry for that canditate.
I hope I am clear, if not let me know
1 entry in range("b4:e4"), per canditate
1 entry in range("f4:h4"), per canditate
1 entry in range("i4:l4") per canditate
1 entry in range ("m4:o4") per canditate
1 entry in range ("p4:r4") per canditate
1 entry in range ("s4:v4") per canditate
it is possible to create a character count in certain cells? I wish to create a worksheet that others can fill in but some cells must have a character count or restriction. It is similar to this - http://javascript.internet.com/forms...r-counter.html
View 3 Replies View RelatedI have following for change event in text boxes to only allow numerics e.g.
Private Sub txtGBP10_Change()
If (Not IsNumeric(txtGBP10.Value) And (txtGBP10.Value <> "")) Then
txtGBP10.Value = Left(txtGBP10.Value, Len(txtGBP10.Value) - 1)
End If
End Sub
Private Sub txtShare10_Change()
If (Not IsNumeric(txtShare10.Value) And (txtShare10.Value <> "")) Then
txtShare10.Value = Left(txtShare10.Value, Len(txtShare10.Value) - 1)
End If
End Sub
Can I add some code so that the user can only add numeric entries to 2 DP (txtGBP) or 3DP(txtShare)
The 'gist' of what I need is for events scheduled (top of spreadsheet), no school can be placed into an event within 29 minutes (because one-half hour apart IS ok). The time increments are on the left side of the spreadsheet (rows). I'm trying to "automate" somehow so that if we try to plug a school into an event too soon from their last event, the spreadsheet won't allow it.
View 2 Replies View RelatedI'm trying to write a macro that will not allow a number to be zero. I have a macro that references a cell that if it is input to zero will create a loop that goes on forever. The thing is this cell will never have to be zero, so is there a way that I can write a macro that if this cell is zero, it gets reset to its previous value?
View 4 Replies View RelatedIn cells B7:B229, I only want the user to be able to enter 1,2,3, or 4. How can I force this on them?
View 1 Replies View RelatedIn column A I have Dates
January till....
The code below count the number of months and then should create only, this amount of new tabs (based on monthlist array)
So if I had Dates from January till October it should create only 10 new sheets.
So far is entering all of them. I know I'm missing a loop somewhere
Code:
Sub AddSheets()
Dim lmax As Long
Dim lmin As Long
Dim lmonth As Integer
Dim cc As Integer
monthlist = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
[Code] ..........
I have a ctrl-q key activated macro (module 2) that successfully clears specific data entry cells in forms on two identically formatted sheets. How do I prevent access to that macro on the remaining three sheets of five in the workbook?
View 2 Replies View RelatedHow can I restrict the user from entering anything other than the date and the proper format required for this input box? I am having a tough time figuring out how to check the input for the right date and format . .
Sub EffDate()
Dim a As Variant
a = InputBox("Enter the effective date" & _
" of the subject's lease using a 2 digit day," & _
" a 2 digit month and a 4 digit year" & _
" (dd/mm/yyyy).", "C/NC Analysis")
Range("U4").Value = a
End Sub
I have the following code behind a save button,
I need to modify it as it currently allows the user to save even if there is no values in D2 or D3. I need it to look at these cells first as well as A17-F25 and if there is no data in any of these cells it either brings up an error message saying you need to enter data in these cells to save!
Also, at present the user has to click on the default save button within excel to save changes made after the initial save, could this be added to the button I have created?
Here is my ...
I have an excel sheet which acts as a form for multiple users, and I wanted to restrict the way certain cells can be filled in.
For example, if the value in Range E12:E100=PD then the values in Range B12:B100 must be blank. Is there anyway to do something like this?
I've a workbook (XL 2003) test-1.xls, and I need to restrict it to a specific computer with ID: my-a1234567k
(shown under ControlPanel::System::Computer Name::Full computer name).
Is it possible to code such restriction in the w/b Open() event to prevent opening the w/b on a different computer?
My computers each has a different OS (Win 2000, Win XP Home, Win XP Prof ), different Excel version (XL 2000, 2003, 2007), etc., and the majority of my workbooks have been developed and would work only in certain environment (e.g.; in XL 2003 only, or XL 2007 only, ... ).
for instance if i type in a particular cell any number 1, 0, 53 .. excel must not allow me to do this...
but there are certain value that have both number and alphatbets eg. 001 michael jordan ... cell should allow such values.
I have an array formula that I am trying to populate through VBA.
Here is the whole formula.
intFirstStyleRow = 63
intFirstDataRow = 3
intLastDataRow = 135
intLastStyleRow = 195
gintBOPromo1Col = 12
gintDataFirstStyleGrpCol = 32
gintDataLastStyleGrpCol = 56
gintDataFirstDptGrpCol = 58
gintDataLastDptGrpCol = 62
gstrcDataWorkSheet = "DATA"
Cells(intFirstStyleRow, gintBOPromo1Col).Select..............................
I have a survey with 2 checkboxes for each question. Users need to tick only 1 checkbox for each question. The checkboxes have been created from Forms toolbar. However the feature of checkbox is such that the user can tick more than 1 checkbox.
Is there a way that for each question only 1 checkbox is ticked? I attached a sample for your reference.
My 10 MB excel file has calculation mode always automatic. It has 100 worksheet. Only a few of these worksheets are enable for calculation, for performance optimization.
Therefore, I use the macro: Worksheets("sheet1").EnableCalculation = False
When needed, I do: Worksheets("sheet1").EnableCalculation = True
Unfortunately, from time to time, the calculation does not work. By chance I realize that by insterting a cell in this worksheet, the EnableCalculation became active. My problem is that I feel this is happening randomly, depending on worksheet, maybe depending on users or on sequences of entries.
I have a range B1 to B4 for user to input value. Worksheet Change Event
However, the range B1 to B4 should not accept any value if the cell on the left hand side is empty.
Eg,
If A1 is empty, B1 should not accept any value.
If A2 is empty, B2 should not accept any value.
If A3 is empty, B3 should not accept any value.
Below code checks for condition in A1 only.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B4")) Is Nothing Then
If IsEmpty(Range("A1")) Then
MsgBox "Please input something under column A."
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
Target.Offset(0, -1).Select
Exit Sub
End If
End If
End Sub
Eg, If I enter something in A1, B2 would accept a value which is not what I hope to achieve. It should be checking for cell in A2 whther it is empty.
Would be grateful for some advice on how to modify the code.
I attached a sample file for reference
Is there a way to force the inputs of a pop-up window to be a certain format? I have a macro that copies and renames a sheet based upon user inputs....but it only uses a pop-up window with dialogue only explaining that that is how they SHOULD name the sheet (DD-MM-YY) without using restricted characters. Moreover, it would be great if it would check the name against names already in the workbook to prevent an error for duplicate sheet names or force it to be whatever DD-MM-YY they input as DD-MM-YY(2) etc.
Sub CopyandRenameSummary()
Application. ScreenUpdating = False
'Copies and Renames Summary
Sheets("Summary").Select
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
On Error Goto ErrorHandler
Dim Myinput
Myinput = InputBox("Enter the Count Date in the format DD-MM-YY (INCLUDING DASHES). You cannot use any of the following symbols: : / ? * [ ]. You cannot prepare more than one count sheet per date input.")
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
I need a formula that I can put into a cell, I assume via data validation, and will prevent the use of any symbols other than a hyphen "-". Spaces are fine. The cell is being used to enter the 1st line of an address.
View 14 Replies View RelatedI am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E.
What I am looking for
1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that.
2) I would like this validation/formula to be applied to all the cells in a particular column.
3) Nice-to-have.
If the user enters a lower case letter, then I would like to convert to upper case.
I need a way to protect my worksheet from any changes but allowing the users to edit certain cells (eg. allowing users to enter data only into columns A-G).
View 2 Replies View RelatedI've written a spreadsheet for others to use, protected it (except for cells where data input is required). Almost like an App for sales people to prepare quotes. I want it to appear fullscreen only with formula bars and headings permanently hidden so feels the experience for the User is similar to an App.
View 9 Replies View RelatedI need to restrict printing & saving functionality on a excel spreadsheet that is shared with a large group of people.
I was also hoping for the restricting save part, to do an AD lookup to see if they are a member of group "X", if they are then allow the save, if they are not then deny the save.
I have the following code linked to a drop down list in my workbook.
[Code].....
It populates textboxes on the sheet with a lookup value result, the lookup value is taken from the dropdown box. The problem I have is any other excel workbooks that are open at the same time, the code is trying to run against those workbooks when I edit them, I can't seem to restrict the code to run only against the workbook in which the dropdown box which the code is linked to, exists in. I have tried moving the code to a module in the workbook but then the code does not seem to work as it does know which workbook the combo box and text boxes which should be filled with the lookuip result belong in
Link to workbook is [URL] .....
I am doing an excel survey using forms. I want to restrict users from completing the survey more than once. i am using below code to input user login details in to a spreadsheet but i am not sure how to restrict them. I want a msgbox or some sort and then it closes the excel active workbook.
code to get username:
Sub GetName
Dim r As Range
Set r = Sheets("UserNames").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
r.Value = Environ("username")
r.Offset(0, 1).Value = Date
r.Offset(0, 2).Value = Time
End Sub
After looking through various forums i found code to highlight duplicates but not sure how this can be modified to suit my need.
Code to find duplicates:
Sub Dups()
Application.ScreenUpdating = False
Sheets("UserNames").Range("A1:A500").Select
Rng = Selection.Rows.Count
[Code] .......
I have a cell (A1) with a number in it. The number has 30 digits. I need a formula for (B1) that will enter the same number as A1 but restrict the number to 28 digits. Basically I need it to cut off the last 2 digits.
View 3 Replies View RelatedIs there a way to restrict a macro to run only a specified number of times during a user designated time period, for example run no more than 3 times during a 24 hour period?
We have a macro that runs to alert maintenance personnel if a certain temperature condition is met in machinery. This condition may happen numerous times during a day but we only need to alert the mechanics once a day if the condition is true.