I have several data val drop down lists, that all have the same input, but for some reason, the first entry in the list will not save to the worksheet. When chosen, after being saved and closed out, the entry disappears when the document is re-opened.
Furthermore, the drop down lists that are linked to another cell WILL save the first entry when chosen.
Is this a programming issue?
How to set up an excel sheet to automatically save data after every data entry? Or maybe after a specified time period?
View 1 Replies View RelatedI have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.
Other cells in the row contain formulas and are locked and the sheet is password protected.
On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?
I am trying to reset various (data validation) drop down lists in a the dummy worksheet attached. Most of the code i have come across clears the cells completely, however i would like it to return to the 'select' option (first on the list) in the list.
I have come across this code, but it does not seem to work:
[Code] .........
I want the clearing code to run from the command button in the 'instructions' tab to clear the drop down lists in the '1. Inputs' tab.
Attached File: Reset drop down list.xlsm
I have a worksheet with two dropdown menus. The first contains months from January to December, the second contains years from 2011 to 2025. Upon opening the worksheet, I would like the entries selected to match to the month and year of the current system date. I know this is relatively easy to implement but, unfortunately, the macro recorder is of no support here. How to refer to dropdown menus in an Excel worksheet and how to select a specific entry from the dropdown menu?
View 4 Replies View RelatedI need to know how i can select an option from a dropdown list and have it drew data from a table and place it in a certain cell based on my choice.
i have attached a sample form.
in the sample form i have two dropdown lists.
Countries and POD
What I need is when i select an option from the POD list the data on the Rate table will appear on the left side in order of course.
Attached File: test1.xlsx
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedI used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies View RelatedI have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
i have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
I am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:
First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses
if I chose Hats it should not show up in the second drop down
second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?
I'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View RelatedI have been making and so i thought saving a module however every time i close the spreadsheet that module is associated with it deletes. is there any way to prevent this from happening.
Also when trying to edit on PERSONAL.XLSB!Module1 i get a particular error stating
"cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command."
The aim of the macro is to save all the sheets in .txt files named as the original sheets.
View 3 Replies View RelatedThe dialog box comes up with file name formatted correctly and shows .xls as file type but when you choose save the dialog box disappears as if the file was saved but the file never gets saved.
View 3 Replies View RelatedI am trying to write a script to import, reformat and save sales files for upload into our master server and had a question.
how to make it store a range as a dim to use later. I would use
Code:
Range(ActiveCell,ActiveCell.End(x1 Down)).Select
on all the columns, but there are often blanks in many of the rows and I am worried about potential errors.
There is a column which always has all of the rows populated, so I was hoping to could store the range of that column and use it for all the others, so they all match length.
I have a workbook with several users. I'd like to present the user with a message when he/she hits the save or save as button, such as "Have all entries been verified?" with a yes or no option.Need to allow save when either button (yes or No) is entered.
View 9 Replies View RelatedIs there a way to save a formula in Excel, or do I need to use the macro function. I would rather not use a macro due to how simple the formula is but I use it often enough that I do not want to retype it each time.
View 9 Replies View RelatedI'm rather new at VBA
I have a small workbook with a userform and some buttons. Each time I press a button a counter increases and the workbook is saved.
This saving seems much too long for such a small file.
I use
Activeworkbook. save
I have two worksheets in a spreadsheet but after I use it I want to be able to click a button and just save Sheet2 Alone. Can I do this.
I have this that works but would need to change it up to just save sheet2
Public FilePath As String
Sub SaveAs()
Dim strSaveAsFile As String, fp As String
FilePath = ""
' Change the FilePath to suit
fp = "S:Depot Outgoing"
Call MakeFolders(fp)
Call MakeFolders(Format(Date, "yyyy") & "")
Call MakeFolders(Format(Date, "mmm yyyy") & "")
Call MakeFolders(Format(Date, "mmm dd") & "")
strSaveAsFile = UCase(ActiveSheet.[B8].Value) & ".xls"
ActiveWorkbook.SaveAs FilePath & strSaveAsFile, xlWorkbookNormal
FilePath = ""
End Sub
I have 2 sheets in a spreadsheet I would like to Save Sheet2 ("Packing Slip") ONLY!
If I can I am trying something like this.
Public FilePath As String
Sub SaveAs()
Dim strSaveAsFile As String, fp As String
FilePath = ""
' Change the FilePath to suit
fp = "S:Projects"
Call MakeFolders(fp)
Call MakeFolders("PCAR " & Format(Date, "yyyy") & "")
Call MakeFolders("PCAR " & Format(Date, "yyyy") & " OUTGOING" & "")
Call MakeFolders(UCase(Format(Date, "mmm yyyy") & "")).........................
I work with alot of reports that are pulled directly from an application.
On each report i need to 'Alt=' at each cost center so that all manual changes are picked up.
However as you can imagine with thousands of rows this could take a while and be very tedious. Is there a quick way to do it?
Subtotals will not work because the costcenters will appear in the same column as the employee numbers.
I have excel 2003 for XP and I have a excel file that is a csv. I used easyfilter to filter my results. (I wanted to delete complete rows that had no information in a certain field in column) Everytime I complete the task and try to save it, it tells me file.csv may contain features that are not compatible with csv do you want to keep the workbook is this format? -to keep this format, which leaves out any incompatible features, click yes. -to preserve the features, click no. then save a copy in the lastest excel format.
My question is this...is there a way to bypass this? Is there a simpler way to delete those rows without using easyfilter so that I can save it after the rows have been deleted.
I have an Excel doc with ~20 tabs. Each type represents 1 type of activities (soccer, tennis, etc). The size of the file (with the 20 tabs) is pretty significant (>1mb), which jams our email traffic.
Is it possible to:
- keep this workbook with the 20 tabs when open by the user;
- let the user select and fill in whatever tab he needs (just one at a time);
- have a button for the user to click that will save only this tab with a different filename (therefore, when sent by email, the file should be pretty small).
I tried to implement many other Saveas macros into it. from other post and members. I can't seem to get it to work. As the macro is, It saves the copy to My Documents. But, I need it to save to the dierctory below. Any help will be appreciated.
',//Tom's-srv01sharedQC DocumentsCustomer Inspection Reports
Sub myBuildDupWB1() 'Static Inprocess
ThisWorkbook.Unprotect
Dim sht As Object
Dim strMyNewWB$, strMyOldWB$
On Error GoTo myErr
strMyOldWB = ThisWorkbook.Name
Workbooks.Add
strMyNewWB = InputBox("New Customer Part Name, below:", "Name Workbook!", "Rename Blank InProcess Op")...........