Combobox Data Capture From Other Workbook

Nov 20, 2007

how data capturing works, using controls. I try to explain what I mean. I have a userform with a combobox that allows to select data from a worksheet in another workbook (database). Using VBA, I'm asked to define the "source" for the combobox. I don't understand the difference between controlsource and row source because when I close the userform, I find only the first field filled in (column "A"), while I would like the entire row to be copied.I also would like to have an "ok" button to allow me for multiple selection. In other words:

I select the desired data from the combobox
I click on the "ok" button ahd this event should copy the data (entire row) selected via the combobox on my worksheet without closing the userform in order to allow me to select other data on the same session. The row to copy data into is selected using the firstemptyrow method..

View 4 Replies


ADVERTISEMENT

Capture The Workbook Name

Dec 6, 2005

I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.

View 12 Replies View Related

Capture First User Of A Shared Workbook?

Feb 28, 2007

Using Excel 2000, I have a shared workbook which is effectively a single sheet, flat database. Rows contain dates, and when certain dates occur, I want to send an email alert to specific users. I have searched and found lots of guidance on that aspect, but am wondering how best to trigger the email alert. At least one, and up to 10 users access the shared workbook at different times of day, and I only want to review the data and send the email alerts to the relevant users once per day.

I thought about a timed event, but cannot be sure the shared workbook will be open. I wondered whether an event in Personal.xls might do it, if I ensure I open Excel every day, but it's not ideal. Finally, I wondered if I could somehow trigger the alerts when the first user opens the workbook - but can't find how to capture that.

View 2 Replies View Related

Capture Active Workbook Events Via .xla Add-in

Mar 18, 2008

I wrote a small .xla add-in for Excel, which puts a command bar on top, and provides several buttons that do some tasks on the workbook that are frequently needed in our office. At some point, I needed to catch the event of a newly activated sheet, because my add-in will do something upon this event (namely, change the state of one of the buttons on its own command bar)

After some reading and searching, I managed to accomplish the task, however not completely. I first want to copy my code. The code has some tags in it, in order to make it easy to follow the flow and find out where the error lies. Here is the class module in the xla, which handles the events of the application:

' Class name is EventClassModule
Public WithEvents App As Application

Private Sub Class_Initialize()
MsgBox "Event Class initialized" ' Just to follow
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "WB Opened: " & Wb.Name ' Just to follow
End Sub.......................

View 7 Replies View Related

How To Capture Streaming Data

Oct 6, 2007

I have streaming data coming into my excel spreadsheet. The data is in 1 cell and its a number that changes almost every second. How can I capture and store this data.

View 1 Replies View Related

Find And Capture Data

Jan 24, 2007

I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case.

Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired.

Sub test2()

Dim xx As Variant

Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value
MsgBox xx
If rng Is Nothing Then xx = 0
MsgBox xx

End Sub

View 8 Replies View Related

Formula To Capture Data In Cells That Are After It

Aug 12, 2009

I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38).

What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value).

I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.

View 10 Replies View Related

Data Auditing - Capture Changing?

Jul 10, 2014

I have an excel file with column stating month and rows are phases ... for each Phases we add the revenue numbers by month and it does keep changes as and when scope changes. However, i am finding it difficult to capture the changes ... is there a way i can set the guidelines to ensure all historical data changes as and when any updates and it should store the historic inputs for consolidation ... i would like to be called as Baseline, Re-baseline1, Re-beseline2 like wise.

Sample attached : sample_auditing.xlsx‎

View 13 Replies View Related

Trying To Capture Point In Time Data In New Fields Each Day

Oct 2, 2008

Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex:

A1 - A4 have "totals" derived from an external source, and the fluctuate daily.

I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time.

I've started with a macro that I would attach to BeforeSave as follows--

View 2 Replies View Related

Auto Populate 2nd Combobox Based On 1st Combobox Data

Mar 30, 2014

I need to populate two combo boxes from excel sheet, the data will be like below:

Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102

So from the above data, one combo box should hold unique values A & B.

On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.

So the data should be like below:

If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.

Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.

View 1 Replies View Related

Formula To Capture Completed Status For Relevant Data

Apr 2, 2014

I have attached the the excel sheet where in i want to calculate the total number of completed data points for one person.

Let's say "Wasim" would have completed 7 tasks and would updated the relevant cell as Completed.

Now in a huge data list How will i find "Wasim" and how do i calculate how many he has completed.

I believe Pivot table would be the answer here but is there any formula which would replace the pivot table?

(Please see the attachment) Example.xlsx

View 2 Replies View Related

Capturing Data: Capture Fleet No & Model From Book 1

Oct 22, 2009

find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.

View 2 Replies View Related

Macro To Capture Data From A Specific Cell Upon Selection Changes

May 22, 2007

I'm trying to do is definetly simple to a lot of you, but I'm struggling on how to get started. I attached a workbook that clearly shows my work! I'm almost there but not quiet. Basically the user will be able to make a slection that will be used to lookup values from a different sheet. Each selection triggers a ranking number at the bottom (red pattern) in the worksheet. Now I would like to capture all scenarios and display the ranking.

View 4 Replies View Related

Capture External Data Refresh Date & Time

May 29, 2008

In the Data tab under Connection Properties, I have "Refresh every x minutes" checked. Question is how do I capture in a cell within a sheet the last time it was refreshed?

View 2 Replies View Related

Capture And Calculate Data Based On Work Week In VBA Or Formulas

Jul 21, 2014

I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.

Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.

For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.

here is the distribution of columns per week as reference.

(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns

(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns

View 1 Replies View Related

Combobox In Shared Workbook

Dec 16, 2008

Please follow the link ..... This works perfectly for me.. But it doesnt seem to work in shared workbooks.

View 14 Replies View Related

How To Load Combobox With List From Another Workbook

Dec 17, 2011

I have this code that works in Word but when I try it in Excel I get a "runtime error 438/object does not support this property or method" on this line: "For Each ish In Sheets("SDF & ER").InlineShapes"

Code:
Sub LoadPEGC()
Dim ish As InlineShape
Dim cbo As MSForms.ComboBox
Dim xlapp As Object
Dim xlbk As Object

xlsfile = "C:fileserverusersTPCentral_TooPE_GC_list.xls"

[Code] ......

View 4 Replies View Related

Workbook Open Populate Combobox?

Apr 3, 2012

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

tried with

Code:
Sheets("Sheet1").select

and

Code:

Sheets("Sheet1").ComboMaaned.list = Array ...

Seems to crash every time.

View 9 Replies View Related

Populate Userform Combobox From Range In Different Workbook

Jul 28, 2012

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

View 3 Replies View Related

VBA - Delete Sheets In Active Workbook According To Combobox Value

May 8, 2014

That particular question has been solved, but now i need it to work with multiple values from combobox.

So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7

The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"

WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.

Run-time error '-2147417848 (80010108)': Automation error

The object invoked has disconnected from its clients.

When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate

View 5 Replies View Related

Userform Combobox Populate From Hidden Workbook

Mar 27, 2014

I'm trying to link combo box's on a user form to a hidden workbook. I placed the workbook into

C:Users[User Name]AppDataRoamingMicrosoftExcelXLSTART

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.

View 10 Replies View Related

Combobox List Lost On Closing Workbook

Mar 31, 2008

Simple combobox query:

step 1. open excel, control toolbar, add new combobox ( named combobox1)
step 2. create new module, with the following

Sub test
With sheets(1).combobox1
.AddItem "123"
End With
End Sub




step 3. run macro "test", combobox1 should be populated, save and close file.
step 4. re-open file, drop down on combobox1, no values to select from.

View 9 Replies View Related

Live Capture Of "Orbits" Race Data

Oct 5, 2006

I have a program running on my computer called Orbits.

What Orbits does is monitor a race track, and via a transponder in each race car, when a race car crosses the start/finish line Orbits will record that infomation.

The orbits program continually feeds data out the port 50000.

I can start hyper-terminal (yes the free one that comes with windows) and create a link the the computer running orbits. I use IP and port (Ex. 10.0.0.1 and port 50000).

As the race is going on I can see the data in hyperterminal: Below is a sample.

$F,9999,"00:00:00","19:52:40","02:09:14","Green "
$F,9999,"00:00:00","19:52:41","02:09:15","Green "
$F,9999,"00:00:00","19:52:42","02:09:16","Green "
$F,9999,"00:00:00","19:52:43","02:09:17","Green "

What I would like to do Is to get this data LIVE into excel, as the race is going on.

I have no idea of to get this data into Excel since It is not saving it as a file.

View 9 Replies View Related

Excel 2007 :: ActiveX Combobox And Shared Workbook

Jul 15, 2014

I have a workbook with a combo box.

ActiveX control? (=EMBED("Forms.ComboBox.1","") They are filled using named ranges on another worksheet and work fine. The problem happens when I share the workbook the combo boxes become completely non-functional. They don't even drop down when clicked.

View 2 Replies View Related

Adding Unique Items To ComboBox From Another File/Workbook

Aug 31, 2006

I need to add about ~200 items to Combo From Other Excel File (with no duplcates).
How to make it.

Private Sub UserForm_Activate()
With cboComp
.addItem "item"
End With
End Sub

View 3 Replies View Related

How To Capture Data From One Cell To Another Based On Another Cell Value

Apr 18, 2014

i am in need of the following.

A B C

9.30am 120 120
9.31am 119.9
9.32am 119.8
9.33am 121
9.34am 120.9
9.35am 120.8 120.8

Here A and B value is keep on changing from external source, when A1=9.30am, the B1 value 120, then C1 should capture the B1 value and stored in it. like wise it captured and stored for every 5 minit or as per requirement.

View 9 Replies View Related

Capture Name Of Data File When Importing Into Another File

Aug 7, 2007

I am using the following code to browse to a CSV file and import it into my current workbook. How can I “capture” the name of the text file (myFile) for future use?

myFile = Application.GetOpenFilename("All Files,*.csv")
If myFile = False Then
Exit Sub
End If

' Open file
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("A1"))
.Name = "DATA"
.FieldNames = True...............

View 9 Replies View Related

ActiveX Combobox - Linked Cell Reference Lost After Save / Close And Re-open Workbook

Feb 11, 2013

Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.

View 13 Replies View Related

Add Row Of Data To Combobox

Oct 14, 2013

I am trying to add a row of data to a combobox list of values but it looks like it will only allow a column of data to be added. Is there a simple way to add a row as the source data or would i need to add each data point using a For Next loop?

View 5 Replies View Related

Capture And Keep Cell Value?

Aug 1, 2012

I have a workbook where data is constantly changing

If two cell values become equal I want to capture and keep the value that was in another constantly changing cell at that time

The IF argument will only caapture the moment and then return to the IF False command

View 2 Replies View Related







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