I have a form that when users hit submit, the data they entered gets transfered to Access (thanks to our MVP SydneyGeek for providing me with a macro!) I want to be able to limit or control the users who sends data, by making sure they enter every information that is needed.
I have a column that has a list using Validation. How do I make certain columns required based on what they choose on the list.. if they don't fill in those columns, I don't want them to be able to click Submit, showing an error that would say "Missing Required Fields".
I have written a macro that prompts the user to chose some files and if they meet certain criteria, it copies them to a specified folder.However, I have run into this 424 error "Object required".
Code: Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST"
[code]....
The error is in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
how to do some complex stuff in Excel but sometimes I don't know how to do some really easy stuff. After I have named a data set of say a certain ten cells in one column, is there a function or a way to input the name and have that data populate a different ten cells? For instance if I have a huge amount of data all coded and I want to take equal but different portions and run them through a template, how can I make it so I just have to enter the a code for any of the data I have coded and have it populate momentarily where ever I need it to go? I'm sure this is very easy to do unless I have explained it inadequately.
1) Can a userform procedure be place in the midst a larger macro, so that it can get input from the user before running the rest of the macro? ie If the user leave the input field blank, the macro will exit will a msgbox.
2) I have a range(A1:A3) that i want my userform to display. This range is dynamic. The user will require to input the corresponding country next to it. how do i code the this? I will need to use a listboxes and textboxes right?
for example: userform shows city on the left, user will need to input the country on the right.
A1 - California (user input on the right) A2 - Shanghai (user input on the right) A3 - Mumbai (user input on the right)
I am trying to create a macro to import a print file and a despatch file into excel so i can format the data. Each files has a date in the file name so i am using an input box for the user to define the file to be imported. My problem is that the user has to enter the date for each file, is there anyway for the user to only enter the date once and it apply to both files, the code i have so far is
what VBA is required to have a combo box in a userform look up data in a different workbook, then populate that data into the drop down list of that combo box for a user to select.
I have managed to successfully create a vlookup for a combo box in a userform that looks up data in a named range in another worksheet within the same workbook and then populate that data into a field in a worksheet within the same workbook. However, I want to change this so the combo box on a userform (in one workbook) will look up data from a named range in a second workbook without opening the second workbook. At the moment, the code I have that will 'submit' the user selected data from the combo box drop down list works.
The code that has worked so far to vlookup data for the combo box from a worksheet within the same workbook is:
Code: Private Sub UserForm_Initialize() Dim pName As Range Dim ws As WorksheetSet ws = Worksheets("vlookupsheet") For Each pName In ws.Range("ProjectName") With Me.cboProjectName .AddItem pName.Value End WithNext pName Me.cboProjectName.SetFocus End Sub
Do I need specify the file path of the second workbook that will contain the data for the combo box as well as the name of the worksheet and named range in that second workbook?
master spreadsheet resides in x directory, up to 100 individuals upload a file into that directory weekly. The uploaded files are from the exact same template, but file-names are slightly different
Is there a way to to extrapolate the data in specific cells from each sheet, to the master sheet. Ideally separated by date of submission (or a date supplied from within the uploaded sheet itself)
I'm guessing at using a variable like "*" in the file name
- but that would include the master file? causing duplicate data
I would like to populate data from 1st File(Name:-Master Data) to the 2nd File(Name:-Calculator) using validation list in the 2nd file in such a way that once the Incentive Calculation is done for any store the same data should get populated to the 3rd File(Nameay Out) automatically without manual copying & pasting.
1)Master Data File(1st File):-Contains all the relevant raw data for incentive calculation.
2)Calculator File(2nd File):-Contains a drop-down validation in cell D4(in the sheet Named:-Hyper) and this drop-down contains the list of the stores starting from H001 to H032. I have got the Sumproduct formula to link Master Data File & Calculator File via this Drop-Down list which is yielding the Incentive Amount.
3)PayOut File(3rd File):-This is the where the storewise Incentive amount should finally be stored. I need help in keeping the respective storewise Incentive amount to respective locations even though different store is selected in the Drop Down Validation in the Calculator File.
I will share a small data pertaining to 2nd File(Name:-Calculator) & 3rd File(Nameay-Out) to further explain of what result is expected.
The following figure shows the results of H012 store(when selected via the Drop-Down validation):- File Name:-Calculator.xls(Sheet Name:-Hyper)
Drop-Down List(=D4)Contains the Store names & links the Calculator File & Master Data File.
The Above Sales & Target achievement for store H012(for FMCG Department) yields the Incentive amount in the same Calculator File(Sheet Name:-Hyper) as follows:- Department(B17)Designation(C17)Incentive Details(D17)Month1(E17)FMCGFloor ManagerIncentive4641FMCGSupervisorIncentive3370FMCGStaffIncentive2276
Now finally the data from the above Incentive Calculation should get populated to the 3rd file named Payout(Sheet name:-Sheet1) as follows:- Month1Store Name(A2)Staff Incentive(B2)Supervisor Incentive(C2)Floor Manager Incentive(D2)H012227633704641H013 H014
H015
Now suppose If I select H013 from the drop-down cell in Calculator file the incentive amount should get calculated and the same should get transferred to the Pay-Out Sheet below the H012 store incentive amount without changing or altering the H012 incentive calculation and so on for H014/H015 etc.
I need to create time sheets for about 30 contract workers.
Each time sheet needs to have their name as well the date for sunday of that week inserted into the form. (once the date for sunday is entered into the sheet, the rest of the dates for the week will be extrapolated out).
Then, once the data is inserted for a single worker, I need that file to be saved using the workers name in the file name.
The script would then open a new time sheet template, insert the second workers name and the date for sunday and save the file. Repeate for next 30 workers.
I have 10 columns in workbook 1 and i am tryin to populate all the data from 10 columns of workbook 1 in to workbook 2. but the prob is if i use the above formula, i have to modify it in all the columns of workbook 2 to get the data from workbook 1. what makes my job easier is if i cld use the above function or any other to populate all the 10 columns of workbook1 in 10 different columns of workbook 2 without having to use the formula 10 different times and modifying it each time. its like writing the function in one cell and all the 10 cells get populated by itself in workbook 2 with the data from workbook 1. I used the above function as there are dupluicates in the data and vlookup dont work in that case.
I have the following macro that imports data from several input files and rearranges it in a master file.I want to change it so that I can use it in each of the input files. Therefore, it should look in the input file for Spreadsheet "XYZ" and rearrange it in Spreadsheet "Data".
I have a workbook (Book B) I want to take information from and Auto populate into another (Book A) if the Type e OR Type f are marked. But only need certain column information from Book B. Let me know if there are any questions I am unsure how better to exlain.
I work for a city that is holding an Olympic style competition for seniors. The athletes are able to compete in several different competitions. When they fill out their registration form, I populate a master roster in excel. The spreadsheet has their basic information and what sport they are competing in. I have the basic information, name, address, sex, etc... And each sport such as Golf, Basketball, Swimming, Tennis, etc.. listed as columns. I put an "X", in the cell if the athlete is registered for that sport, if they are not registered I leave the cell blank.
Is there a way I can get all of the athletes for one sport copied to a new workbook, or at least spreadsheet for each individual sport? So I have a workbook/spreadsheet named Basketball, and I would like to get all of athletes registered for basketball on its own.
I want to populate the listbox dependant on the worksheet names, skipping the first 4 worksheets The texbox should highlite a value in the listbox (if exists).
I have spent many hours trying to research and create a code to enable me to create a master workbook with very little success
I have attached the "master workbook"!!!
I have a file C:workjobs
In this folder I have many workbooks all named differently normally with the customers name and a description of work completed ie: Mrs Jones, Bathroom refit, etc and the file is updated with new workbooks all the time as we complete new jobs
I need create a macro that automatically starts when the masterfile is opened that grabs all the information needed from all the individual workbooks and then inputs the totals into the cells in the masterworkbook so I can then analyse the information
The master workbook I have attached is exactly the same layout as the individual workbooks
Ive got 12 sheets, one for each month, and I've got a combobox in each sheet, and Ive got a macro for each combobox, that makes it jump to the correct sheet number. The problem is that when I try to populate the comboboxes under the workbook_open() sub, it doesnt work.
Tried
Code: Private Sub workbook_open() ComboMaaned.List = Array("Januar", "Februar", "Mars", "April", "Mai", "Juni", "Juli", "August", _ "September", "Oktober", "November", "Desember") End Sub
I have workbook "Invoice" which has one Combobox. This Combobox1 should takes its data from another workbook /closed/ called Firms. I need it to be closed. What code i should write and where, so that when i add new firm and close the workbook Firms, so that the new value will be added to the combobox1?
I am looking to fill out a combobox (say combobox1) in a userform from a set of ranges located in a different workbook (that is in a different location).
For example, the cells that would need to be in the combobox are B5:B41,D5:D40 and F5:F45 and the file location is W:WCWORKCENTERS.xls
The attached workbook requires users to enter a date on the SAT sheet (B3). I would like that date to always represent Saturday (the first day of our work week) Is there a formula I can use to validate if the date entered by the user corrleates to Saturday or is there a way to auto-populate the workbook with the Saturday's date?
Then hide the worksheet which essentially hides the workbook as only 1 sheet is present in workbook. This should make the workbook available every time a user opens Excel
Now when i initialize the form i get an error
Run-time error '1004': Method 'Range' of object'_Global failed
However when i unhide the worksheet the form runs fine. Do i need to declare the combo boxes differently than shown below The range "Tools" does exist on the hidden workbook.
I have a one excel workbook with columns customer id , customer name, customer address and another excel work book with columns customer id , account name, account type.
I am in need to produce a new excel workbook with datas from above said 2 workbooks with customer id column as reference by macro programming in excel.
So the new workbook should have data from the columns customer id, customer name and account name
I have a workbook template that will have multiple tabs. I would like to have it that when you open this workbook the date populates in A1 for each tab and is static. At that point they can save with another name leaving the template intact so when you open it again (on another day) it will populate with the current date. Is this possible and if so how?
I have a single registration sheet that lists names, contact information, and the class someone is in on a single sheet.
(Name, Number, Email, Class 1, Class 2)
I am hoping to automatically populate other sheets in my work book based on classes... essentially making automatically populated rosters. For example, everyone who has Math listed in either Class 1 or Class 2 would have their entire row (with contact information, etc) copied to the "Math" sheet.
I have a master workbook with 20 worksheets. 5 worksheets in the worksbook are distributed to field reps, but the remaining 15 worksheets are not. What is the best practice for receiving the 5-worksheet workbook from the field rep and importing the data into the 20-worksheet master workbook? If I merely use Copy Sheet I am able to copy the worksheets into the master workbook but then I have to remove the pathname references in each of the copied worksheets in order to map the data in the master workbook - which is quite cumbersome.
Running excel 2003 and would like to make a macro for a workbook. Example I want a macro to say (see the big truck), I can record this ok but can't get it too play in any cell on my workbook. I want too be able and input this in any blank cell on the workbook. Is this possible? when I try this I just get nothing in the cell.
I have a spreadsheet that requires an entry into an Input Box before the rest of the workbook is created. Users click on the button, an Input Box appears and they must enter data. Once the data is entered and they click OK the file is created and saved to their local drive with the name ".xls" and then the sheet closes.
What I want is to have the file that is created be named as whatever the user enters into the Input Box field.
