I have created a userform which is initiated off a button in her spreadsheet, where if she enters an order number, it will display the values from the row containing that order number. I have made it so she can load from the excel sheet the values into textboxes, where she can change values then save them back into the excel sheet.
I have also include the ability to insert a new row at the end for a new order.
Everything works fine except the date values and I'm a bit stumped, I have searched but can't find anything that helps (I thought DateSerial helped but it didn't).
I've had two problems. the first was that the day and month swapped, I could load 1st Mar 2007 from the excel sheet and when stored back it changed to 3rd Jan 2007. This I presume to be due to US and UK date formats, I am in the UK.
is it possible using vba ideally with a button marked save, to save a single worksheet and name it, in CSV format
Then I would like to be able to do the reverse load a CSV file back in to a work sheet
Expanding upon this
1. Click a command button with caption “save”
2. Take the name of a given sheet, in my case DataToUpLoad and append it with a number to give a sheet name of DataToUpLoad 1 the first time it is save and DataToUpLoad 2 the next time and so on
3. Click another command button with the caption “load” interact with usual dialog box.
My current work around is to copy a sheet open a new work book past the sheet in the save that work book as CSV
The reason this is required is to mate up with the php on the server side
I have created a workbook for a quality check and it has about 50+ worksheets in the workbook, which contains data, formula,graphic & vba function. The size of the file is about 4890kb. It's works as planned, except, it takes about 1 mins ++ to load, save & even exit. Is there any suggestion to eliminate this issue, apart from creating a few workbook to accomodate the number of sheets/item in the project?
Intermittent run-time error '75' Could not find the specified object.
Debug shows VBA Code that simply loads a form called controls
Sub loadcontrols() Load Controls Controls.Show End Sub
Generally works upon restart. Can load and unload the form numerous times. Seems loading the form after a few minutes results in the form failing to load generating the error. Found no consistent cause thus far.
Results in a hard crash: Restart Excel as only option.
The Form Code for Controls loads a modeless transaprent form. Has been working for months. Don't know if this error is new or has been there all along.
Option Explicit Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Declare Function ReleaseCapture Lib "user32" () As Long Private Const GWL_STYLE As Long = (-16) Private wHandle As Long 'For moving the form Private m_objCFormSkin As CFormSkin 'For transparancy
I've created a sheet to estimate a job project and now I am working on a new sheet to keep track of our estimated totals compared with actual expenses.
What I need is to make a macro that allows you to browse the PC and let the user select the excel sheet where all the Estimate Values are stored. These stored values are pulled up and posted onto the new workbook.
Please pull A1 from the estimate book and put it in B4 of the new workbook
I'm doing a Vlookup and when I copy down the formula Excel asks me to update the values by opening a new document. But this I can't or don't want to do - but I can't get rid of the box that asks me update the values with a new file.
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.
No-----Name-----Date---Price----Cost 10-----Dino------etc-----1--------2 10-----Dino------etc-----3-------5 shaded cells to here ishh 4-------7
No-----Name-----Date---Price----Cost 12-----Bino------etc-----4--------6 shaded cells to here ishh 4---------6
No-----Name-----Date---Price----Cost 13-----Rino------etc------5-------5 13-----Rino-----etc-------6--------6 shaded cells to here ishh 11-------11
The difficulty is due to the fact that the number in No column varies, for example there may be 2 rows of 10 or 4 rows of 10. What is the quickest way to format it all too, so have a border and font size etc all the same?
At present I have to manually insert blank rows between the different numbers in the No column, copy column headings, then manually sum up Price and Cost, then insert borders. But for hundreds of records.
I found this code to insert 3 blank rows manually for me when the numbers in No column are different:
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.
See the attachment. When the macro runs. The workbook is saved in a folder with a new serial number. That is fine but I need to debugging the code. Since I need to save the WB name with the time and date to be also picked and named when saving. Refer to cell B1.
Is it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
Is there a way to automate the date and time in one cell upon saving? I have seen some posts elsewhere upon this subject, but here is the twist: this is a shared workbook with each tab allocated for a different person. Each person needs to update their worksheet and have the date and time update upon saving. The date and time would appear in the same cell for each sheet. I am fairly certain that this cannot be done with an Excel formula, so I would appreciate if anybody had any VBA suggestions. (I am just starting to learn VBA
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
I have a workbook with several worsheets in it. I wish to have the first worksheet as a list that will be hyperlinked to the other worksheets within the documant.
Is it possible to save the workbook so that it will open at the first worksheet every time irrespective of what worksheet you were on prior to clicking save.
I have a estimating workbook with a number of worksheets to provide a estimating process. this includes the final worksheet with the estimate on. This is fine if i am printing it to send to the customer or sending as a PDf.
However i need to save as a stand alone excel workbook. But all pre-formated and only with the values in it and not the formulas. It would be easiest for the users if it could be done with a button on the quote sheet (ie done with as macro as some users are not very IT literate and do mess up).
i have an excel table with embedded listboxes (Control Toolbar element). The user selects from listbox and has to send this file for approval. but when she saves the spreadsheet, the listbox-selection is reset to the first item. I know, that this is common for Excel, that the Listbox-selections are not saved. So I think the solution is:
1) to save the position of the shown listbox-item like
Cell(1,1).Value = Listbox1.TopIndex.Value
2) at reopening the saved file just to put this value into the Listbox like
Listbox1.TopIndex.Value = Cell (1,1).Value
But I don't know how to access this Control Toolbox objects from VBA. I've read some previous posts and then tried with
but it doesn't work. The simplest solution would be to use the LinkedCell property, but the users do not always click on the ListBox to select the Item, they just scroll with the mouse and then save the file, so that ListIndex and TopIndex are not the same.
I would like to set up my workbook so that I can save it by the combination of two cell values. The cells are A2 (which displays a country name) and E10 (which displays a string of numbers) and would like to specify the file to where to save the workbook which is:(G:BusUnitsShipping_RecievingInternationalInt'l Order FormsSpecific Orders). The following is a code I have tried that is close but not quite right:
Below is the syntex I am using in the array... and the source file.. when opened will calculate the values. The problem arises when I close the source file and values return to this " #Value". My entire frontend file is nothing but arrays and links compiling data from multiple sources and everything works except cells that have the below array in them.
why my cell values will not save upon saving and closing both my frontend file and source file?
Refer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell() Dim strName As String
On Error Goto InvalidName strName = Sheet1. Range("V77") ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
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?
I have a table of times which shows how long it took someone to process a form. If this value is longer than 10 minutes the cell next to the duration time contains the value 'Break Over Ten'. This is only true when the time is outside of 12:00 to 14:00. If the break is within this time period it shows 'Lunch'.
Is it possible to sum all of the time values that are over flagged as 'Break Over Ten'? This is how it looks
Event Date Start Time End Time Duration Breaks Duration Of Break 19/12/201308:43 08:43 00:00:35 00:00 19/12/201308:43 08:44 00:01:03 00:01 19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13 19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13
I was thinking of using SUMIF but can't work out how to total the values in the duration of break column when the cell to the left reads break over ten. Is it possible to do this?
Over the weekend I did some edits to an Excel file on my home computer. This morning I did some edits to the file on my work computer. When I try to access the VBA code, I get the message "Error in loading DLL", which is error 48.
I'm trying to load an array with numbers from 3 to SheetCount. If someone wouldn't mind giving me a nudge in the right dirrection, it'd be appreciated.
I have created a Excel userform. Since it has lot of controls, it takes good amount of time to load.
Is there a possibility of displaying a progress bar till the userform is loaded.
Google search has shown me ways to create a progress bar using Excel Forms. However, I am looking for a way to show the progress bar till the main userform is loaded.
I want to create a spreadsheet which will include a column with email addresses in it.
Here's my problem.......
I want users to be able to click on an email address (like a hyperlink) and by doing this open their mail client (in this case Microsoft Outlook) and send an email. I would like the mail client to load a specific template just by clicking on the link from Excel. I'm not sure if this is possible, but there doesn't seem to be many things you can't do on Excel.
