I have a worksheet, attached, that is a supplier response to a purchase order. Column E contains the original qty ordered of each part number, Column H contains the original price. Now, I've created a quick macro, (in the workbook), that copies the original values to the confirmed values, and the formulas in columns G and J calculate the qty backordered, or flags the part as a price change.
After copying the original PO values to the confirmed values, the supplier would need to go in and make whatever changes are necessary. For example, we ordered 3 and they shipped 0 of a certain part, or the price has changed.
What I would like to do is create a macro that pops up an input box where the supplier enters the Keystone part number, (column A), then enters the new quantity or new price, and those items are automatically changed on the response form. I picture it in the form of 3 input boxes, in the first box the buyer enters a part number. A second box pops up asking if it's a qty change or price change. They would enter the value in a 3rd box, and that value would automatically be changed on the response.
I have the inputbox so i can set a string value, When the inputbox Cancle button is pressed i want to exit sub, If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname. If the inputbox has a value do >>>>>>That>>>>>
I need to have an inputbox for users to enter multiple store numbers. What I need to do is actually filter out the store numbers that was entered into the inputbox my code so far is:
'Inputbox to filter out new stores Newstore = InputBox("Enter New Store Numbers seperated by a space", "Enter New Stores", "119 120") x = Split(Newstore, " ") For i = 0 To UBound(x)
Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Array(x) _ , Operator:=xlFilterValues Next i
I want to use the SaveAs function using the Inputbox results. The user will input a date like 5-30-06. Then I want save the workbook as "WE5-30-06.xls". I can't figure out how to do this b/c it wants to use 5/30/06 which isn't allwoed in naming conventions. BTW the inputbox name is dWeekend.
I have found a base macro that has most of what I want the only problem is I need it to search column A by the data entered by the user in the inputbox and send out an email if it meets all the requirements. I also need it to be able to be able to do that search by only the first letter of the company name (example, only needing to put in A in the inputbox for Amber Inc.). If I remove the red text the macro works perfectly but just runs down the list on the spreadsheet.
Column A has company names, Column B has email address' and Column C has the company contacts first name.
Here is my delimma. I am using the standard InputBox for my users to input the number of errors found for each category in a record from a daily report. The problem I having is that every time the user types in data using the InputBox it overwrites the previous days numbers. I need to set up the InputBox code so that after the user has input the numbers for that day that the next time the InputBox is used it selects the next column to the right and continues to do this for each day.
i have a worksheet that gets data from web - its automated, but the website goes through a tunnel - with security - requires username and password
the Query Runs ok and smooth but i have to login manually by right clicking on a table where the query is and selecting "Edit Query" so i can login, excel vba doesnt save passwords for that part...
one way i find it possible to automate that part too would be to use maybe like the sendkeys statement to pop up the context menu from right clicking the mouse button, or like the options button on the keyboard.
I found this one:
[Code] .....
It works but the context menu comes exactly from where the current mouse position is..
I needed it to do the right click on a specific range in the worksheet (where the web query is)..
I get a report that I have to manually edit everyday. I've done some VBA, but not sure where to start with this one.
The attachment has two sheets, first is what I get and the second is what I want. I'll explain the process i have to go thru now and maybe I can get some help to automate it.
First -Add the tech number to the first column for each job for each tech. 7988 us the first tech number.
-Then delete every row that isn't a job. -Add a column with the install date.(report data near the top.) -Add a column and populate it with the system, which is the sheet name. Thats why its example.
I have a userform that I have created to enter new part information to a spreadsheet but I am wanting to also use the userform to edit exsisting part information. My spreadsheet consist of part #, name of part, description of part, date manufactured, and date sold (Column A-E). My question is can a userform be used to edit information that is already present on the spreadsheet? I am looking to edit any entries that do not have a date in the date sold field.
I have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel.
I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation)
I am looking to pull out an outcome based on the user inputting two criteria, below is an example:
A B C D
[Code].....
So if the user inputted P1 into cell H2 and inputted 9 in I2, J2 would show Pass, but if they inputted P5 into cell H2 and inputted 9 in I2, J2 would show Fail.
I'm coming along nicely with my user form. My last issue was trying to get it to search/recall data from the "Data" tab. This works perfectly when only 1 record is found, and if multiple line items are found, a message box pops up and tells me how many records are found that meet the criteria. However, I am now struggling to get the code to work properly to past the details of those records into the list box, so that the user can select which record they want to edit.
I get a Run-Time Error '1004' Application-Defined or Object-Defined error and it points to the section in red:
Code: Sub FindAll() Dim strFind As String 'what to find Dim rFilter As Range 'range to search Set rFilter = Worksheets("Data").Range("E3", Range("h65536").End(xlUp)) Set Rng = Worksheets("Data").Range("E3", Range("e65536").End(xlUp)) strFind = Me.cbosearch.Value
I work in a small team of 5 people, but each of us is very busy and get emails from our manager asking us to complete different tasks. I have made a userform in excel for our manager to create the tasks in and put all the details in sheet1. This creates each separate task on a separate row. Now I want each of my colleagues to log into the sheet and be able to retrieve a task with the same userform. I am giving each task it's own ID, so I want to be able to search by this and the userform will be populated with the task details. I also have combobox's with the status of each task (Completed, In progress etc.) I want to be able to change these in the userform too.
VB: Private Sub UserForm_Click() Private Sub CommandButton1_Click() Dim LastRow As Object Set LastRow = Sheet1.Range("b65536").End(xlUp)
I am trying to edit the data in the actual pivots rather than editing in the data source. i know there is vba code to enable pivot table editiing. how to enable this
I have several Excel files that I am about to change some data in, so I need to create an macro to speed it up. All sheets should be updated besides the last one. The number of sheets differs between the files. The data that should be changed is in column C, starting at cell C10. But the number of rows differs. If the data in the cell begins with a Q, the Q should be removed, and if it doesn't begins with Q it should be skipped.
I want to be able to select and edit a range of data on a userform. I am trying to pull the information that matches a cell range (Named as Action) into the respective textboxes. But I have fallen at the first hurdle and can't even get that working.
I have created a form in VBA using txt boxes and cmd's. I want the abitility to edit the existing data on my excel worksheet using the form. Currently, I can add data, but not search through the existing data on the form.
I have been using the excel tips and it has been great, but I am stumped at this point. This is my first time to every post a message.
Any way to edit data that has been populated in a Userform Listbox? I am trying to create a userform which has a multicolumn (3 columns) listbox and data being populated from Sheet1!A:C. I am thinking that there would be an Edit button where when clicked the data for the row that is selected in the listbox is shown in 3 textboxes (one for each cell on the respective row) on the same form which can be edited. When the user clicks Save. The Listbox would be updated with new values (e.g. write over the values in Sheet1.
I am using the below code to pull information from multiple sheets into one master sheet. Currently, it is funcitoning fine with one slight problem. In each of the sheets I have formulas copied down until row 500. However, some of the sheets may only have formulas populated up until row 50 for example. edit the below macro to where I am only pulling in the rows that have populated formulas?
Code: Sub Consolidate() 'Author: Jerry Beaucaire' 'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
I have a spreadsheet that summarizes variations on a project. On the "Variations" tab a userform pops up that asks the user to select from 3 options:
1. Create new variation This launches another userform that allows the user to enter the necessary information and create a variation sheet. This userform updates "VarSummary" and also creates a new sheet for each variation created. I have been able to do all of this so far. The summary sheet "Variations" tab uses the data on the "VarSummary" tab. This is the code I used to add variation
Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("VarSummary") With ws iRow = . Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'Copy the data to the database .Cells(iRow, 1).Value = Me.txtVarRecNo.Value .Cells(iRow, 2).Value = Me.txtDate.Value .Cells(iRow, 3).Value = Me.txtChg.Value .Cells(iRow, 4).Value = Me.txtSrcRef.Value .Cells(iRow, 5).Value = Me.cbstatus.Value .Cells(iRow, 6).Value = Me.DirQty.Value .Cells(iRow, 7).Value = Me.DirVal.Value............................
A.) As a user is entering data into a userform two specific values need to be checked in the existing data sheet.
Textbox1 ( date) Combobox (operation name) {4 tabs on from textbox1)
They relate to the data stored in columns 'A' and 'E' respectivly.
What I'd like is, as the user exits combobox4 to check if the values already exist. If they do; fill the coresponding textboxes with those values and allow the user to make any amendments, then have it SAVE to the SAME row, would a record number be necessary to accomplish this?
B.) For a future development, Im thinking of applying the same principle to a different project, where 3 values need checking.
Possible complications are that below combobox1003 there will be 12 other comboboxes(a value title, from column header) with associated textboxes alongside(value previously entered by user).
There will be 22 possible values for the comboboxes(the values will be the same column headers) The user will only ever have the choice of making 12 entries though.
Does any one have any spare code laying round for this one..
I have created an example of a "Waiting List" for the college, everything I had done seemed to be working before I left for my holiday (apart from the search facility as I was still doing some problem solving, compiling and coding for it).
The Idea is to : Open Excel Document and be greeted by the "Hello" Userform
[Problem #1: This does not automatically show since I have returned from holiday, though there seemed to be no problem with it prior my escape.]Choose from three options;"Save and Exit" saves any changes on/in the database and closes the document"Insert Data" opens "Userform1" to allow users to enter a new row under the exsisting rows of data.
[Problem #2: The data in the sheet was all entered using this feature, but yet again since I have returned from holiday it keeps adding the data only on row 2]
[Problem #3: How to do Auto Formatting i.e. Postcode in Capital Letters]"Search" will open the "Search" userform which I hope to allow users to choose up to three column headings and enter the value to search that column with the parallel textbox value.
[Problem #4: The data which meets the criteria the most is listed into the listbox, if you select a piece of data (double click or "selected") it opens a new userformThe "Edit/Enrol" Userform allows user to Edit, Enrol or archive the row of data.the last Userform I created "Course" is for an admin of the workbook to choose a course and it be auto populated by the information in the worksheet "Code" and create a Mail Merge.
I have a workbook which contains a list of data on the first worksheet. I need for a macro to copy the name in each row,along with its data in that row and paste it into the corresponding locations on a template located on another worksheet. It would then save the file using the date (col. a) , then the words " Scorecard for" and then the name (col. b), then loop for each row until it reaches the end of the list.
I located this code here here.However I dont know how to modify it to fit my needs. I dont have 2 separate categories for two score cards in column b. It would all run off one scorecard and each row of data.
In my work book there is 2 sheet, 1 (sfc)is data sheet where i puts all the data & second (size roll )is for a pivot table, now I am not able to make the update of the pivot table, I want to update the pivot table when ever i enter or edit any data of data sheet .
See the attached file , in sfc sheet Column W,X,Y,Z contain the data for pivot table on "size roll sheet "
I noticed that when I click on a cell in my Pivot Table a new worksheet pops up with the requested information (essentially a filtered version of my data source depending on what cell I clicked) is there a way for that information to appear on the same worksheet as my Pivot Table? And if so can I also edit what parts of the data source appear?
I'm working on an Excel based product. As part of the security process I would like to require the user to enter a product id key into excel. Then the spreadsheet would compare the entered value to some kind of online database to verify the id key is valid. Finally, the spreadsheet would update the online database so that the id key is no longer functional. How easy is this to do?
I know verifying that the id key is valid is fairly simple.
Here is my biggest issue: How to get Excel to update the database and of course what form would this database take. Maybe its a table on a webpage, or google docs. Again the issue is how would I be able to update the table.
I have created a simple userform that is linked to a button on sheet1. When the data in the userform is submitted, I set it up so it goes into a database under sheet2. The problem is, I don't want anyone modifying the entries in the database. Usually I would protect the sheet with a password, but when I do that, there is an error when the userform is submitted. The only thing I can think of is to hide sheet2, which doesn't seem like a great solution.
Is there any way to protect my database from being modified yet still allow the userform to be linked to it?
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm
