Restore Validation Using VBA
Nov 28, 2007
I want to do is allow a user to choose entries from a data validation list and then they can remove the validation to make some edits to the selection. I have it set up so that they can do this by clicking on a command button that will remove validation for the selected cells.
What I want to now do is to be able to restore the validation in case they have made an error. My first thought was to have a hidden "Master" sheet that has all the relevant validation in it and then when the user picks a range to restore validation to, just copy the cells from the "Master" back to the working sheet (it should be noted that the range to restore validation to may contain cells with different validation). I'm sure the code for this is quite simple but I am still learning. The steps I want to do are:
1. Store the range of cells chosen on the worksheet
2. Go to the Master worksheet and select the same range
3. Copy the range from the Master worksheets back to the same range on the original worksheet.
View 2 Replies
ADVERTISEMENT
Mar 26, 2007
i made XL App settings in a way suits my needs...........
Is there a code to restore these settings when i reinstall the XL App ?
View 3 Replies
View Related
Sep 28, 2013
If I erroneously saved over an excel document that is stored on an external drive, is there any way to restore the previous saved document, or am I screwed.
The thumb drive is plugged into a computer that gets backed up onto a network server, but I assume that the external thumb drive is not backed up with the c drive and r drive on the server.
View 3 Replies
View Related
Mar 19, 2014
I'm a beginner to VBA and I have a dataset with different loan rates for different schools. So far I can highlight rows based on their rate and order them from least to greatest.
What I want is to be able to restore all of the font to default (i.e. undo the highlighting) without changing the format of a specific column of cells (My G column, with the loan rates, goes from Percentage format to General format)
I'm using Selection.ClearFormats, which changes the format of the cells to general as well as it only restores the format of the cells I highlight, rather than the entire worksheet
So basically, how can I delete all formatting of all cells without changing the cell format (Percentage vs General)?
View 1 Replies
View Related
May 26, 2014
i accidentaly deleted excel file how can is recovery it
View 2 Replies
View Related
Jun 27, 2007
In a worksheet, I should have selected the region where data is present, and center align all the cells. Instead, I selected the entire worksheet and did the same, so that whenever new data comes in the relative cells are already formatted. This has caused the workbook size to increase dramatically. (So, I assume that changing a cell property from the default value uses some memory). Is there a way to restore the format of the blank cells to the default values, so that memory is freed and the workbook size decreases again?
View 3 Replies
View Related
Aug 13, 2007
I have a procedure that stores the Commandbar names and visibility settings in an array on open. Another procedure hides all Commandbars on open and restores their original visibility settings on close. For some reason the "Standard" and " Formatting" toolbars don't return to visible on close.
My error log says "08/13/07 18:21:37 [QA.xls]MCommandBars.bRemoveMenus(), Error -2147467259: Method 'Visible' of object 'CommandBar' failed". I have tried manually testing the hide/unhide procedure with hard coded settings and checked the correct variables are being sent and all seems to be correct.
I am pretty confident that the procedure to store settings is correct but have included the code just in case, also just noticed the "Standard" and "Formatting" toolbars are the only two visible at programme start.
Public gsaMenus() As String
Public Function bMenuSettings() As Boolean
Const sSOURCE As String = "bMenuSettings()"
Dim bReturn As Boolean
Dim iCounter As Integer
Dim cbrMenu As office.CommandBar
View 9 Replies
View Related
May 21, 2014
Any way to restore the default reference libraries that are checked the first time you open Tools => References in VBA in Excel 2013. I think I accidentally unclicked something that's necessary to run the program, and I can't find a "default restore" option or anything of that sort.
View 8 Replies
View Related
Feb 26, 2012
I want to disable the three buttons (minimize, restore and close) excel.
[URL]
View 14 Replies
View Related
Apr 1, 2008
I need to create a macro that whenever the user clicks the restore down button it will not restore down, but either do nothing, or make the application display full screen. The following code runs when the workbook opens and whenever a different sheet is activated and I would like it to run when the user clicks the restore down button.
Private Sub Workbook_Open()
Application. ScreenUpdating = False
Application.DisplayFormulaBar = False
Application.ShowWindowsInTaskbar = False
Sheets("01").Select
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With............................
View 4 Replies
View Related
Jun 11, 2014
I managed to implement a macro that adds a time/date stamp in Column A when any of the subsequent cells in that row are changed. With this now working, I really need the ability to restore the undo history in case a change to the data needs to be reverted.
Sub procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If c.Column > 1 And c.Column < 21 Then
Cells(c.Row, 1) = Now
End If
Next c
Application.EnableEvents = True
End Sub
View 2 Replies
View Related
Nov 7, 2007
I haven't used List Boxes much previously but decided to use one in this instance because I wanted the capability to make multiple selections. I used the following in a list box called "ListBox_Analysis_Code":
Private Sub ListBox_Analysis_Code_Change()
Dim iPtr As Integer
Dim sTemp As String
View 9 Replies
View Related
Nov 11, 2008
A2's validation is dynamic as it's selectables varies based on the selection made by the A1 Validation. (=indirect(a1)).
My problem is this....once if have selected from both validations...if I go back and change the A1 validation to a blank (or empty value) or clear the contents on that cell....validation A2's value remains as it was. I would like it to recognize that A1 is blank and also become blank (or goto an empty value).
View 2 Replies
View Related
Sep 26, 2007
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
View 9 Replies
View Related
Aug 19, 2009
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies
View Related
Feb 15, 2014
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
View 1 Replies
View Related
Jun 30, 2014
I had some of values in Column A, B & C for ex: column A has brand name, column B has model name and Column C has sub_model name here i have a limited values i need to make it as drop down list but i had a problem with the below formula.
=IF($F$2=Sheet2!$H$2,al_v,IF($F$2=Sheet2!$H$3,am_v,IF($F$2=Sheet2!$H$4,au_v,IF($F$2=Sheet2!$H$5,be_v,
IF($F$2=Sheet2!$H$6,bmw_v,IF($F$2=Sheet2!$H$7,bg_v,IF($F$2=Sheet2!$H$8,cv_v,I
F($F$2=Sheet2!$H$9,ch_v,IF($F$2=Sheet2!$H$9,ch_v,"")))))))))
i need to add some more ifelse but the validation don't allows it.
View 2 Replies
View Related
Apr 3, 2009
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies
View Related
Apr 6, 2013
when I select a data validation value in cell A3 of sheet 1 will change the data validation value in A5 of sheet 2, and verse vice.
Also in the same attached file, I want to solve another formar issue. The needs is expained in the file.
View 8 Replies
View Related
Jul 1, 2013
I have a list for example with two variants "YEs, "NO"..
Is there any possibility to choose YES or no in any cell an reflect the same value in another list on another sheet.
View 8 Replies
View Related
Sep 9, 2006
I'm not sure if its possible to do what I want. I'm trying to do it without VBA as my users might have a high macro security setting.
I have a validation dropdown in A2 where they pick 'yes' or 'no' for FLSA. I would like B2 to be a dropdown (validation, listbox, whatever) that would change based upon A2. If A2 is 'yes' I want to use the data in A5:B7 and if its 'no' use A10:B12. Ultimately I want B2 to equal one of the numbers from A5 - A7 or A10 - A12. However when they click on the drop down they see the combo of column A and columb B as column A has no meaning for the user.
View 9 Replies
View Related
Apr 18, 2007
I want to make a cell validated so that a user can only enter a value that is either below -0.1 or above 0.1. This is not necessary the next but is desirable to set a maximum of 999.99 and -999.99.
View 4 Replies
View Related
Mar 3, 2013
I'm trying to set up a spreadsheet for users to update with details of customer service problems. A few of the columns have data validation against them (the master lists are on another worksheet) but I want to be able to copy the validation to a new row when created.
I've found this macro which seems to do the trick in terms of creating a row and copying the validation - but there's a couple of things that I'd like to amend...
VB:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LR As Integer
If Target.Column > 1 Then Exit Sub
[Code].....
Firstly, I want to be able to create a new row above what was entered previously. The macro works fine as it is, but as the spreadsheet grows and grows, I don't want users to have to scroll down to the bottom of masses of information. I'd rather have it so that every time a user double clicks in cell A2, the data shifts down but the validation that was in row 2 remains in row 2.
Also, at present in the macro above the value in column A for the new row is 1 greater than the row previous. I'd like that to still happen even when the new row is going above the previous data. Trying to prevent duplicates is another reason why I'd like the macro to only run if cell A2 is double-clicked.
View 1 Replies
View Related
Apr 17, 2013
I inherited a spreadsheet and the date validation does not work. It checks for the formatting of the date being entered, and when anything is entered is returns the pop-up error message. so even when a correctly formatted date is typed into the cell the error message prevents the user from entering the new date. I have checked and cannot find the problem. I even tried formatting the cell with Crtl-1 to ensure the date being entered was formatted right, but still does not work.
This is the code snippet for three columns I am working with:
VB:
columns("G:G").Select
With Selection.Validation
.Delete
.add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="format(""m/dd/yyyy"")"
.IgnoreBlank = True
.InCellDropdown = True
[Code]....
View 1 Replies
View Related
Apr 4, 2008
I have a spreadsheet (attached is an example).
The problem I have is that there is a check cell in column J that either "A" "B" or "C" can be entered and the total in column K can be used in different sheets in the workbook.
Here is the part I cannot get to work, If "C" is selected in the check coulmn then Columns G,H,I, are not allowed to contain numbers and a warning dialog should appear on screen. I have tried numerous ways to get this to work and have given up
View 9 Replies
View Related
May 27, 2008
I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...
Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?
View 10 Replies
View Related
Feb 19, 2009
Is there any chance someone could post an example file for this? I cannot figure out how to set it up.
View 14 Replies
View Related
Jul 22, 2009
I need a code/formula/validation that needs to go in cell C3 and D3. If B3 contains the following, Transactional Accounts, Notify Me and Enhanced Notify Me than there has to be a number/reference number in C3 and D3. If there is no reference number than a warning needs to pop up informing them that they need to put in a reference number or ID number before they continue. If there is no reference number or id number they cannot move on, or something of that sort.
View 2 Replies
View Related
Aug 3, 2009
Basically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.
eg.
A1: 10
B1: =if(A1=10;"A";if(A1=11;"B";if(A1=12;"C";"Invalid Entry"))) ....
View 9 Replies
View Related
Jan 19, 2010
Hi I have a column where a cheque number can be entered I need to check on entry that the cheque number has not been used before further up the column.
View 7 Replies
View Related
