Putting Data Collected In UserForm Back Into Worksheet

Apr 14, 2013

I'm trying to set up a relatively simple UserForm, which will collect the name of a project from a ListBox, a project update which will simply be text entered into a TextBox, and a estimated completion percentage (factor of 10, 10/20/30% and so on) which will come from another ListBox.

I've managed to get many of the basics working, for example an OK button and a CANCEL button, but there are a few bits that I'm still finding rather tricky.

I have the selected date in the percentage listbox going back into my chosen place in the spreadsheet quite happily, but I can't get it to work for the project name listbox (I think it's because the options come from rowsource and weren't entered manually like the other one). I'll paste my code at the end so you can all see how I've done it (and have a laugh)!

I have also just about managed to get the data to go to the right place in the spreadsheet. I want the data to appear in columns, column 1 for the project name, project update in 2 and percentage complete in 3. I have this working, but it for any reason one cell becomes blank, my code uses that cell, and that could cause entries to become mixed up. I want the UserForm to enter data on the first entirely empty row.

Here's my code:

VB:
Private Sub Cancel_Click()
Unload ProjectUpdate
End Sub

Private Sub Clear_Click()
Call UserForm_Initialize
End Sub

[Code] .....

View 7 Replies


ADVERTISEMENT

Use Userform Collected Data To Open A File

Oct 29, 2009

Hi, I'm trying to use a userform to open a previously saved file to avoid having the users dig through the folder and maybe work on the wrong file.

I have a file I have created to test if the code works, but the macro keeps giving me the reponse I set in case it doesn't find the file.

The data for Account (Account1 and Subaccount1) is coming from a text box, as well as for category (Category1). The data for Company is an optionbutton frame with 4 options for the user to select from.

View 6 Replies View Related

Stripping Out Columns Then Putting Back In

Sep 18, 2009

Here is a simple exercise that is necessary only because of the large amounts of data involved.

I have a spreadsheet of columns with budget and actual data. I need to strip out the budget columns (marked as budget) and total column. then add back in at a later date.

It all has to stay in the same spreadsheet.

I know it sounds strange but believe me its necessary.

I was thinking of moving them to the right side of the spreadsheet. Then back at the later step.

I have attached a simple spreadsheet showing the data on sheet1 and how it should look when stripped on sheet 2. and the later step would move them back to sheet3(which is the same as sheet 1).

View 14 Replies View Related

Copying Data From UserForm To SpreadSheet And Back Again?

Feb 21, 2013

We created a database for our employees who have security items and other things we need to keep track. We created a spreadsheet where data was entered on Sheet2 (Entry Form) and then by clicking various buttons would be thrown over to Sheet1 (MasterList) and put in alphabetical order. Then last week he said lets change it to a User form where i can put all the data, be able to tab through my text boxes and make the whole process of entering information faster and more efficient.. but now hes gone and I'm left with his macros and all this stuff i don't really understand! I have been doing pretty well on my own but im stuck "Search Employee" Here is what i have...


VB:
Private Sub SearchEmployeeButton_Click()
LastName.Value = Range("A500").Value
FirstName.Value = Range("B500").Value

[Code]....

use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.

View 2 Replies View Related

VBA Code To Repopulate All Data Back To Userform?

Mar 31, 2014

Any formula to repopulate the data back to userform for editing. I need the data to populate when a record is selected from a combo box [named URN for unique reference number]

View 5 Replies View Related

Paste Data From Userform Back Into Spreadsheet

Jan 3, 2013

I have a "txtQuoteNo" box used in my userform "FrmQuotation", and this is recorded in column A of my Central Data spreadsheet (with a fair slug of other data from the userform).

I can currently recall the data into the userform from the spreadsheet using the QuoteNo, but when I need to resubmit it back to the spreadsheet, I can't see why the following code posts this new data onto a new line rather than the blank cells in the corresponding row of txtQuoteNo and column A (if you see what I mean...)

Set wb = Workbooks.Open("M:Central Taxi Data.xlsx")
With Sheets("Data").Range("A:A")
Set cell = .Find(myVal, LookIn:=xlValues)
rw = cell.Row
End With

RowCount = Worksheets("Data").Range("A" & rw).CurrentRegion.Rows.Count

[Code] .......

View 3 Replies View Related

Force Focus Back: Pop Up A Userform To Provide Instructions And Collect Some Additional Data From The User

Feb 18, 2009

I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.

The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?

View 2 Replies View Related

Formula To Calculate Number Of Work Weeks The Data Has Been Collected?

Dec 27, 2013

I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.

View 3 Replies View Related

Putting A Table Into A Userform

Feb 18, 2010

I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

View 5 Replies View Related

Putting Date In UserForm

Nov 26, 2012

Is there any way for me to put a date in the userform? I created 3 combo boxes (day, month year). I would want it to appear as one date in one single cell in the another sheet as the date the entry was updated in the userform.

View 3 Replies View Related

Double Click Row To Return Data To Userform For Edit Then Return Back To Sheet

Jan 30, 2014

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‎

View 14 Replies View Related

Putting Enable Macros Button Into Userform Type Display

May 12, 2013

In reference to my other topci here: Userform Error

In this workbook I have successfully made it act like a userform calculator, the user does not ever see the actual sheets in the workbook, their only interface is the Userform calculator I created.

This is exactly how I want it, they do not need to see the sheets, nor do I want them to.

This leads me only to my next problem... Some of the users obviously wont have Macros automatically enabled, what I am wondering is, is there a way to put the normal "Enable Macros" button which appears along the top of a macro containing workbook when opened, I want to put this button into a userform like display without showing the workbook at all.

So here the process I envision:

1. User double clicks the file
2. It opens and automatically goes invisable (like it does already)
3. If the user does not have macro enabled, a userform like box appears and says "Please enable Marcros to user" with a button below for "Enable" and "Exit"
4. If exit is pressed excel closes
5. If enabled is pressed Macros are enabled and then they will automatically see my calculator userform like normal.

As you see, at no point do I want them to see excel or a workbook, just userforms so its not like they are using excel at all.

I have searched around the net and found a few ideas to tackle this, mainly one that has a "Prompt" workbook that shows when macros are disabled, but that is not ideal for me. However perhaps I could just edit this "prompt" code thing for my situation?

Here is the prompt sheet code I talked about:

Code:
Option Explicit
Private Sub Workbook_Open()
With Application

[Code]....

How to use:

Open an Excel workbook Select Tools/Macro/Visual Basic Editor in the VBE window, select View/Project ExplorerSelect the This Workbook module Copy and paste the code above into this ModuleNow select File/Close and Return To Microsoft Excel Don't forget to save your changes...

8. Name one of your sheets "Prompt"

View 2 Replies View Related

Putting Spinbuttons In A Worksheet

Nov 14, 2008

The code below places a spinbutton on the worksheet. The linked cell = F10 This works ok. The orientation is vertical and max is 100
However I would like to set the orientation to horizontal and the max value to 1500. Which commands must be added for this in the macro below? I work with Excel2000.

View 4 Replies View Related

Grabbing Name Of Worksheet And Putting It In Cell A1

Sep 19, 2008

This seems simple but I can't figure it out. I want to grab the name of a worksheet and place the name in cell A1.

View 9 Replies View Related

Search Worksheet For Data In Multiple Textboxes On Userform - Display In Temp Worksheet

Dec 23, 2013

I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.

The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.

E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)

row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567

Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".

View 4 Replies View Related

Next And Back Buttons In Multipage Userform

Nov 7, 2011

I have placed next and back buttons in the userform. I have a total of 8 pages.

The problem is that page 2 and 4 is not active. And I cannot see why. In the properties of the pages the data is exactly the same at all pages.

Are there errors in my code?

Code:
Private Sub cmdNext_Click()
MultiPage1.Value = MultiPage1.Value + 1
End Sub

Private Sub cmdBack_Click()
Dim intPage As Integer
intPage = MultiPage1.Value
Do

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

View 2 Replies View Related

Can't Switch Tab Back In Multipage Userform

Nov 17, 2011

I have two pages in the userform. What I was trying to do is that certain textbox (txtDate) needs to be entered on the page1 before going to page2. The user can still select page2 however I want the vba to bring the page back to page1 if the data is missing.

Here is the code:

Private Sub MultiPage1_Change()
If MultiPage1.Value = 1 And txtDate = "" Then
MultiPage1.Value = 0
txtDate .BackColor = &H40C0
End If

It didn't work completely - the tab selection on top was on page1 however all the controls shown are still the controls under page2. I have to manulally click page1.

I also tried MultiPage1.Page(0).Enabled=True but it didn't work either.

View 3 Replies View Related

Copy Worksheet Data In Userform To Worksheet?

Oct 24, 2011

I have embedded a worksheet called Rawdata" into a userform. Is has a number of text boxes on the userform, all the text boxes copy correctly to a worksheet called Rawdata, however i cant get the spreadsheet data to copy from the Userform to the sheet called Datapad.

The worksheet in the userform is from A1:G600, this data needs to be copied back to the Datapad worksheet B2:F601.

code so far:

Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet

[Code]....

View 2 Replies View Related

Modeless Userform :: Set The Focus Back On The Range

Aug 1, 2008

I call a userform (modeless), and then i'm trying to set the focus back on the range that was selected when i showed the userform. you would think i could just define the range and then use rnge.select like this....

set prevrnge = selection

UF.show (vbmodeless)

prevrnge.select

but it doesn't take the focus off the userform....

View 9 Replies View Related

Activation Of Userform Hide Worksheet And Closing Userform Activates Worksheet

May 14, 2014

I want activation of userform to hide worksheet, but as soon as the Userform is closed, the worksheet should show.

I have attached file to aid.

View 1 Replies View Related

Change Userform Textbox Back Colour If Contents Are Percentage

Jul 14, 2012

I need to change the backcolour of some textboxes on a userform that is populated with percentage figures from a sheet, ie:

10% (a positive percentage) textbox backcolour = red
-10% (a negative percentage) textbox backcolour = green

Below is some code showing my attempts up to now, but obviously I can't make it work.

VB:
Private Sub Textbox1_Change()
If Me.Textbox1.Value < 0 Then
Me.Textbox1.BackColor = vbGreen
Else
Me.Textbox1.BackColor = vbRed
End If
End Sub

View 5 Replies View Related

Send Data From Userform To Worksheet AND Send Userform Fields In Email?

Jul 12, 2014

I have used a database template from this site and changed it to suit my needs but I have a bit of a problem with some of the code. I know how to update the worksheet with the relevant userform text fields and in another project I did I have successfully sent userform text fields in the body of an email.

For this project I want to update the worksheet AND send an email at the same time. However, using the two pieces of code together is causing an error that I can't seem to solve (using my very limited vba knowledge!). The code I am working on is below and I have highlighted the line that is getting the error message. C

VB:
Private Sub cmdSubmit_Click() 'Submit new record
Dim ws As Worksheet, lRow As Long, Str As String [code]....

View 1 Replies View Related

Go To Another Worksheet And Then Come Back To Original

Aug 21, 2014

I'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.

[Code] .....

View 14 Replies View Related

MAXIMUM + SUM + IF: Max Amount Collected In One Day.

Jan 13, 2010

I have some data that i would like to sum up. In my sheet I have columns for VEHICLE, DAY, HOUR and AMOUNT. The DAY can repeat itself. I would like to know the max amount collected in one day. I don't know how to build the formula but I believe an array formula is the most adequate way of doing this

View 5 Replies View Related

Getting Data From Userform To Worksheet

Jul 15, 2012

I have a Userform which has 3 'CONFIRM' buttons to pass the data from userform textbox 'ActDate1' (and 2 &3) to the worksheet 'Mod Schedule'.

In Column B from Row 7 onwards is a list of Names - one of these names will have been in the userform combobox 'SlctStu'.

I need vba such that when I click CONFIRM, on ActDate1, this value passes to:

The cell which is offset from the cell which has the matching name from combobox 'SlctStu' in the userform by the following formula:

((ModSend - 1) * 3) + 1

where ModSend is a variable set by my userform Me.Modsend.Value (a combobox).

So, for example, if the ModSend value is 3 and the name "Chuck Norris" is in the combobox 'SlctStu' in my UserForm, when I hit CONFIRM for ActDate1, I want ActDate 1 to go to the same row on sheet 'Mod Schedule' in column B from row 7 onwards where this name "Chuck Norris" also appears (a dynamic named range called 'StuModList' contains all the names that can appear here) and then offset across the worksheet by ((3 - 1) * 3) + 1 = 7 cells

View 2 Replies View Related

Add Data To Worksheet From UserForm

Nov 4, 2009

I am trying to create a User Form to make data input easer for the users.

At this point, I am having trouble figuring out how to “place the data”.

My workbook has categories in which data is organized. For instance A10 might say “Toyota” and B11 would be Corolla than all of row 11 will have data supporting the Corolla.

Same for the “Dodge” category, only it starts at A60 and B61 would be something like Challenger, etc.

I have a code to place the data in the first empty row but this doesn’t work very well for me.

{ iRow = ws. Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row}

I will have a drop down list of all the categories in the User Form which the user will select. From this is there a way to search for that category and then find the first empty row or insert a row within that category and then the rest of the data can follow?

View 9 Replies View Related

UserForm Data To Go To Next Row In Worksheet

Jan 17, 2008

In the code below, all I'm trying to do is get the next record from the userform to go to the next row in the worksheet and so on. I looked through some of the threads and even tried (as underlined) but I just can't seem to get it to work.

Private Sub TEST_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'Prompt user before adding record
MsgBox "Are you sure you want to Add record?", vbOKOnly, Verify

'Add data to worksheet
ActiveCell. Offset(0, 0) = txtFruit.Value
ActiveCell.Offset(0, 1) = txtFruit_Number.Value
ActiveCell.Offset(0, 2) = txtFruit_Color.Value

'Clear userform
txtFruit.Value = vbNullString
txtFruit_Number.Value = vbNullString
txtFruit_Color.Value = vbNullString
txtFruit.SetFocus

' Find emtpy row
lRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
End Sub

View 3 Replies View Related

Compare Columns For Name Collected From User

Apr 21, 2009

The attached file contains a list of customers from 2 years in column A and B respectively. I want to write a sub that takes a single argument customerName(a string variable) and it checks whether the customer is in both lists. If so, I want it to display a message and bolds both instances of the customer's name. Otherwise, I want it to display a message that the customer's name is not on both lists. I also want to write a sub that uses an input box to ask for a customer name, then calls the original sub with the customer's name as the argument.

View 4 Replies View Related

Add Data From Userform To Cells On Worksheet

Oct 29, 2011

I have a userform that has 1 combobox and 2 text boxes. i need the two text boxes to go to specific row and a specific column based on the entry placed in the combobox.

I can move data from a userform to a database if i am adding it to the next row, but here, there already is data and i want to add to existing data.

I tried application.match, to find the text in the combobox, but i can't seem to figure out the syntax to add it to column 13 on the same row.

View 5 Replies View Related

Capturing Data From Userform To Worksheet?

Aug 30, 2012

I have a macro that is starting to get pretty intensive and beyond my knowledge base. Currently is takes the needed data from sheet(1) and copies it to a newly created "Sheet2" in a specific format. Once the macro is done formatting "Sheet2", it shows both userforms for the next part of this macro.

One userform (UserForm1) is for inputting barcode data into rows on "Sheet2" (this is where I am running into problems). I cannot get the userform to capture the captions to the needed cells in "Sheet2".

The other userform (UserForm2) is for a visual representation of the error check. This will check for differences in Sheet2's column data. If a row's data in Sheet2 doesn't duplicate as expected it will flag RED and an image to show in the associated frame in UseForm2.

UserForm1:
Plate ID (PlateIDLabel goes to "PCR Plate ID" header column in Sheet2)
Plate Location (PlateLocationLabel goes to "PCRLocation" header column in Sheet2)

Currently the userform is coded to recognize prefixes for correct input into label textboxes.

Attached it the workbook with macro/userforms.

VB:
Option Explicit
Private Sub CommandButton1_Click()
Sheets.Add.Name = "Sheet2"
ActiveSheet.Move _

[Code]....

View 6 Replies View Related







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