Enter Data To Correct Column Using Userform?

Jun 29, 2014

I have started a user form called submit stock. What i am trying to work out is if i can use this form to enter the data to the worksheet using the form to specific columns depending on the first choice.

View 8 Replies


ADVERTISEMENT

Sum Macro With IF Formula - Enter Correct Value Into Column

Dec 8, 2011

I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.

I want the Macro to do the following THREE THINGS:

1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”
2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”
3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:

= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.

Here is an example of how the formula will work:

I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.

Here the DATA SET:

Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ

This is what is happening in Column Q Explanation of Formula

Row 2
Times
Cont
2
28 28 n/a

n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".

Row 3
Times
Cont
1
404 1194 413.4740369

O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L

[Code] ...........

View 9 Replies View Related

Userform To Enter Data

Mar 15, 2007

I have a developed a UserForm to enter data into Excel. I can get the UserForm to enter data in row2 under the headings I have created. I have a Next button on the UserForm, which I want to go to the next row when pressed to enter dta in row3, then row4 etc. Is there anywhere I can find examples of how to do this?

View 9 Replies View Related

Userform To Enter Data On Worksheet

Sep 14, 2006

I have sheet (say Sheet3) that is in essence a Database. I need to create a Userform to insert a row above the highlighted cell and insert the data on the Userform in that row.

Userform would have 3 textboxes to enter data, a submit button and a cancel button. (the 3 boxes titled Part, Price,Vendor)

In the Worksheet the 3 Columns are
A C E
Part Price Vendor

View 6 Replies View Related

Enter / Lift Data From Spreadsheet Into UserForm?

May 27, 2014

I have created a spreadsheet which uses a User Form to input data and then give it a reference number (this bit works fine).

I am now trying to use a User Form to enter a reference number which will then be searched across my spreadsheet, I then want all the entered data relevant to that reference number to appear in a User Form along with additional fields for entry of additional info.

In this User Form, the original data could be modified but more importantly, the remaining data added before then being placed into the main spreadsheet.

This is a booking in system where the person presents to a receptionist and the initial data is entered. Once that person has been dealt with, the remaining details regarding their visit will need to be inputted, hence this request.

View 2 Replies View Related

Enter Data In Cells While UserForm Showing

Oct 30, 2006

is it possible to enter data on a spredsheet while a form is opened or must the from be closed first. I thought I was smart to have a form remain open so users could change parameters but at the same time allow them to enter data in uprotected cells.

View 2 Replies View Related

Enter Data To Multi Sheet Through Userform

May 9, 2007

Enter Data To Multi Sheet Through Userform. How can I enter Data from entryform to multisheet?

View 6 Replies View Related

Password Userform - Login Function To Enter Data

Mar 10, 2014

I have a userform that numerous people need to fill in. I want to add a login function so that each person has their own login detail. Their will be a command button called login. Until they login in they cannot enter any data.

View 4 Replies View Related

Enter Data From Userform Into Cells Starting From Specific Cell?

Feb 3, 2014

I need to add something to the code below to have my first row of data entered into cell "Q8". As it is now the data is starting on row Q9 instead of Q8, but the rest of it is filling in correctly.

[Code] .....

View 1 Replies View Related

Userform To Enter Data In Different Locations Depending On Selected Options

Jan 22, 2009

MONDAY
xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc.
Hours State1
-
Hours State2
-
Hours State3
-
etc.

TUESDAY
xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc.
Hours State1
-
Hours State2
-
Hours State3
-
etc.
WEDNESDAY
xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc.
Hours State1
-
Hours State2
-
Hours State3
-
etc.

Each team leader would then input hours in each state each day on their column. Make sense? Easy Peasy...

My question........... is it posible to have a userform where a TL would select their name (Column) and Day (Monday=Row 3, Tuesday=Row23 etc) from a dropdown and then input figures in txt boxes to submit them in the correct location?

View 9 Replies View Related

Check Cells Are Correct Format And Contain Correct Data

Jan 26, 2014

We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.

I envisage something like an output report:

#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################

View 4 Replies View Related

Keep Comments Column Correct After Refreshing Data Columns?

Dec 16, 2012

I have an worksheet that refreshes every day from a .csv report. I would like to be able to have a comments column at the end that users type in. The problem is how to keep the comment next to the row it was on before the refresh.

When it refreshes there will be new rows, rows that are no longer there and all in a different order.

There is an Order Number column that can be used as an ID column.

View 3 Replies View Related

Display Value Of Intersection If Enter Row And Column Data?

Dec 19, 2013

Basically, I have a simple - but large (48Mb) sheet with UK mileage distances between towns. As the are 1,000 towns and therefore 1,000,000 cells of data it is a little tedious to scroll to the desired town. see sample attached (this is not the real file obviously!)

What I would like to do is type in a start and end point, and have Excel look for the intersection and display the value of this.

View 1 Replies View Related

Copy The Data From Other Column And Paste With ALT Enter

Mar 10, 2009

i face a problem with this, refer to the Book1.xls. i don't know how to explain it. it is very simple but i don know how to do it.

View 2 Replies View Related

Userform Fill In Correct Sheet

May 29, 2009

I wrote my program with several user forms and private subs. All the results was going to my Report sheet. However, now, I want to be able to run those userforms in another sheet. All the results are coming up in this selected sheet. Is there a way that I can program to for all the results to go to the report sheet without going back and fixing every private sub?

View 3 Replies View Related

Userform Activate Label In Correct Format...

Nov 19, 2009

Userform Activate Label in correct format. I have the following code in a userform activate

View 2 Replies View Related

VBA Code To Find Date In Column A And Enter Data In Cells On Same Row

Dec 26, 2013

I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.

VB:

Private Sub CBSecure_Click()
Dim my_name As String
Dim r As Variant
Dim l As Long
sFind = DockDoorCal.Value
If Trim(sFind) = "" Then Exit Sub

[Code]....

View 4 Replies View Related

Enter 4 Columns Of Data In Row Then Shift After Hitting Enter?

Jun 1, 2014

I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False

[Code].....

View 4 Replies View Related

Userform Textbox Using VLookup Date Could Not Give Correct Answer (40850)

Nov 6, 2011

I created an UserForm that looks for Student Name his/her Conferenece Date through a VLookup.

The following is my code:

Private Sub cmdFind_Click()
With frmConference
.txtTranslator.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 6, 0)
.txtDate.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 5, 0)
.txtTime.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 4, 0)
End With
End Sub

The txtTranslator and txtTime works correctly, however txtDate give me a number '40850' not a Date?

How can I change this number for the actual Date?

View 9 Replies View Related

Using Enter In Userform?

May 30, 2012

I have this series of userform modules that error check the user's input of a date value.

Code:
Private Sub txtDate_Enter()
txtdate.SelStart = 0
txtdate.SelLength = Len(txtdate.Value)

[Code]....

Everything works fine, but I'm looking to improve it's efficiency. Most people will by habit press [ENTER] after inputting the date in the textbox txtdate control. As it is now, for the code to execute after the value is entered is to push the {SUBMIT} button.

What would I need to do to allow hitting [ENTER] to do the same as clicking submit?

View 7 Replies View Related

Userform Tab Using Enter Key?

Mar 7, 2014

I have a userform made with excel. I need to tab to the next field (in the tab order) when I press enter instead of having to press the tab key. Is there an easy way to do this. Im not that savvy with VBA.

View 1 Replies View Related

Enter With UserForm To 1st Empty Row

Aug 16, 2008

I have an excel sheet with a couple of rows/columns of data in it (i've attached the sheet to my post (zipped in two parts)). Since I'm not familiar with VB. I want to simplify data entering procedure and I need a simple Userform for that purpose. That userform should enable me to enter data in the first row which has an empty cell in Column C. I have found a relatively decent userform on the internet and incorporated it into my sheet, but it's not working particularly well (it allows me to enter data only in one row, not in two or three). If I hadn't been clear enough, try to enter data in my sheet and you'll see what's the problem

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
iRow = ws.Cells(Rows.Count, 3) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 3).Value = Me.txtLoc.Value
ws.Cells(iRow, 4).Value = Me.txtdate.Value
ws.Cells(iRow, 5).Value = Me.txtQty.Value
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtdate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

PS you'll notice that I have a Command button/Macro that inserts new rows in the middle of the already entered data. It perfectly suits my needs since it also copies the formula from the Column L to the new rows. That code needs no repairs.

View 5 Replies View Related

Enter UserForm TextBox Into Cell

Dec 24, 2007

I am having trouble using User Forms.

I am trying to enter data using User Forms to apply to a specific cell.

For example the user enters text or a number value into a text box and clicks a command button to submit that information into a specific cell. In this case D43.

View 4 Replies View Related

Enter Key On UserForm To Next Control Not Working

Apr 23, 2008

I have a User Form that is used to collect data. CommandButton1 loads the User Form data into the worksheet and CommandButton2 clears all data from the Form in preparation for entering the next record. Immediately after a user first opens the Form, the very first time CommandButton1 is clicked, the Enter Key stops working. At this point data can be typed into any TextBox on the Form, but when the Enter Key is pressed the cursor remains in the TextBox. (The Enter Key will not move the focus to the next Textbox in the Tab Order. Also, if I alter the code to set the focus on a CommandButton as the active control instead of a TextBox, pressing Enter on the active CommandButton does not execute the CommandButton's macro... the same behavior as a TextBox; nothing at all happens when the Enter Key is pressed even though the CommandButton has focus).

At the point when the Enter Key stops functioning, if the user presses 'Alt-Tab' to leave the Form and then immediately uses 'Alt-Tab' to return back into the Form the Enter Key suddenly works again and continues to work correctly from that point on even after CommandButton1 is clicked. Again, the Enter Key stops working ONLY the first time CommandButton1 is run immediately following initially opening the workbook and Alt-Tabing into the Form immediately corrects it. I saw one other post in this forum with this same problem in 2003, but unfortunately it did not get answered.

View 2 Replies View Related

Userform To Carry Out Action When Enter Is Pressed

Aug 26, 2005

I have a userform that contains a number of controls. When I press the enter / return key, I would like this to have the same effect as hitting the 'OK' button i.e. run some code. At the moment, hitting enter sets the focus on the next control in the tab order.

View 5 Replies View Related

Close UserForm When Enter Ket Pressed In TextBox

Apr 6, 2008

I have a userform with a textbox in it. Once you type what you want in the textbox, you click on one of two buttons - either Accept or Cancel. It would be much neater if the userform would close when the user hits the carriage return in the textbox. Anyone know how this can be achieved? Kjartan Auto Merged Post Until 24 Hrs Passes;Doh never mind folks, I figured it out...

Private Sub Userform1Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Userform1.Exit
End Sub

View 2 Replies View Related

Countif Formula: Added The Correct Amount In The Correct Cells

Feb 15, 2010

I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.

View 4 Replies View Related

User Inputs Information Into Userform When Click Enter?

Mar 21, 2012

Say a user inputs information into the user form, when they click enter, it enters all the data on the next available line in a workbook.

Is is possible for the user to click a cell on a previously entered row, and have the userform populate with the existing information?

example.
say the user has to fill out 3 separate areas. 1, 2, 3. However the user only has data for 1 and 2. They go ahead and enter the infromation for 1 and 2 and click submit to transfer to sheet. Now later he gets information for area 3, can the row the information that he previously entered, lets say column 1, row 1, and it reopens the userform with the information for 1 and 2 prefilled from what he entered previously?

View 9 Replies View Related

To Create A Userform With A Textbox To Enter A Product Code

Jun 11, 2009

i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel ,

View 9 Replies View Related

Userform - Match Date And Enter Value At Adjacent Cell

Jun 21, 2009

I have this userform:

DATE : [TextBox1] WITHDRAWAL: [TextBox2]

[Commandbutton1]

In my sheet I have a list of all the days in a year in the cell range of AJ12:AJ376.

Basically I want to find the date in Textbox1 within the range of AJ12:AJ376 and enter the value of Textbox2 in a adjacent cell. The adjacent cell would be in column AT. This would be achieved hopefully via a code using the commandbutton.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved