Userform data

I have three userforms, each of them getting their data from three different 
worksheets. Most of the work I do is in a fourth worksheet where the three 
userforms should pop up. To load each of these userforms, I used :

UserForm1.ListBox1.RowSource = "b7:b" & items_quantity

Obviously, this works as long as the active worksheet is that where the data 
is located.

Is there any way of collecting this data referencing the worksheet where it 
is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" & 
items_quantity)). I tried that but it doesn't work.

Thanks for your help.
0
Cordobes (8)
12/3/2005 2:23:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
414 Views

Similar Articles

[PageSpeed] 10

UserForm1.ListBox1.RowSource = Worksheets("Sheet1").Range("B7:B" &
items_quantity).Address(,,,True)


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
news:1BA91537-BCCF-4372-987B-D66DB9B7ED79@microsoft.com...
> I have three userforms, each of them getting their data from three
different
> worksheets. Most of the work I do is in a fourth worksheet where the three
> userforms should pop up. To load each of these userforms, I used :
>
> UserForm1.ListBox1.RowSource = "b7:b" & items_quantity
>
> Obviously, this works as long as the active worksheet is that where the
data
> is located.
>
> Is there any way of collecting this data referencing the worksheet where
it
> is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
> items_quantity)). I tried that but it doesn't work.
>
> Thanks for your help.


0
bob.phillips1 (6510)
12/3/2005 2:32:19 PM
Bob,

Thank you very much for the quick response. I trie what you suggested and it 
is giving me this message <Subscript out of range>.

"Bob Phillips" wrote:

> UserForm1.ListBox1.RowSource = Worksheets("Sheet1").Range("B7:B" &
> items_quantity).Address(,,,True)
> 
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
> news:1BA91537-BCCF-4372-987B-D66DB9B7ED79@microsoft.com...
> > I have three userforms, each of them getting their data from three
> different
> > worksheets. Most of the work I do is in a fourth worksheet where the three
> > userforms should pop up. To load each of these userforms, I used :
> >
> > UserForm1.ListBox1.RowSource = "b7:b" & items_quantity
> >
> > Obviously, this works as long as the active worksheet is that where the
> data
> > is located.
> >
> > Is there any way of collecting this data referencing the worksheet where
> it
> > is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
> > items_quantity)). I tried that but it doesn't work.
> >
> > Thanks for your help.
> 
> 
> 
0
Cordobes (8)
12/3/2005 4:33:03 PM
You have to use the worksheet name that you actually have, Sheet1 was just
an example.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
news:B2B78800-7213-48D2-8511-A22D4FFB557D@microsoft.com...
> Bob,
>
> Thank you very much for the quick response. I trie what you suggested and
it
> is giving me this message <Subscript out of range>.
>
> "Bob Phillips" wrote:
>
> > UserForm1.ListBox1.RowSource = Worksheets("Sheet1").Range("B7:B" &
> > items_quantity).Address(,,,True)
> >
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Cordobes" <Cordobes@discussions.microsoft.com> wrote in message
> > news:1BA91537-BCCF-4372-987B-D66DB9B7ED79@microsoft.com...
> > > I have three userforms, each of them getting their data from three
> > different
> > > worksheets. Most of the work I do is in a fourth worksheet where the
three
> > > userforms should pop up. To load each of these userforms, I used :
> > >
> > > UserForm1.ListBox1.RowSource = "b7:b" & items_quantity
> > >
> > > Obviously, this works as long as the active worksheet is that where
the
> > data
> > > is located.
> > >
> > > Is there any way of collecting this data referencing the worksheet
where
> > it
> > > is located ( ie UserForm1.ListBox1.RowSource = sheet1.range("b7:b" &
> > > items_quantity)). I tried that but it doesn't work.
> > >
> > > Thanks for your help.
> >
> >
> >


0
bob.phillips1 (6510)
12/3/2005 5:48:25 PM
Reply:

Similar Artilces:

Pivot Table Form
Hi Everyone, I have a Pivot Table Form in Access 2003 and need the blank/null cells on the data axis to display "0". This is easy to do in Excel but I have searched through all of the options in Access and cannot find it. What is the VBA code required to set this option? Can anyone suggest a good reference book or website for coding Pivot Tables with VBA? Many thanks, David Hi David, can't you use the calculated value NZ([myvalue],0) instead of the value itself? -- Kind regards Noëlla "David Saywell" wrote: > Hi Everyone, > > I have a Piv...

Creating a userform?
I would like to enter data to a speadsheet with a userform. I want enter the data in a form that will automatically insert the data to the correct cells in my spreadsheet. And then allow me to enter new data on the next line below. Is this possible? I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 42 towards the top of my home page. In here I have tried to explain, as clearly as possible, what you need to do to set up and use a Form in EXCEL 2007. There is a simple worked example for you to work through if you want to. Please d...

Open Userforms from a Dropdown list
Hello, and thanks for the help, I have several userforms and would like to place the names of the forms into a dropdown list to select the form i wish to use is this possible. again thanks ...

Use spreadsheet as data source
Hi, We have two workbooks, Bk 1 contains information relating to production scheduling and manufacturing information. Bk2 is a template used to generate a ticket (workbook) containing specific information relating to the individual order. Is it possible to transfer information between the two. Eg on Bk1 there is a packing spec worksheets for items, what size outer to use, and how many items in that outer as well as other information, can we interrogate this (search by item code) and then "autofill" in cells marked "Outer" and "Number" in Bk2 (ticket). Tha...

Data identification
Hi All, I need help on this one: I have a one column table, with numbers, for example: 100 200 150 111 111 100 100 and some of those numbers have negatives (in the same table/column): -100 -100 -150 -200 -111 What I need is to list of everything that left as the positives and negatives are removing themselves. In result I should have as follows: 111 100 Any idea how to do this? My table has over 8K lines..... Many thanks, Marcin Marcin Peciak wrote: > Hi All, > > I need help on this one: I have a one column table, with numbers, for > example: > 100 > 200 > 150...

Missing Data From Excel Query
I'm querying an Excel database into another Excel spreadsheet. I have two fields where I want users to input into the database. This input data is not coming across in my data, even though I'm including the field. Database: Account Mar Apr Variance Explain Explanation Printing 546 556 -10 Explain Printed spring catalogue The explanation field is where the input is, everything else is a formula. But even if I make the input data into a formula, it doesn't come across in the query. If I make the input data a number such as 5%, it comes across, but any text d...

opening a userform from a userform
Is there a way to open and use a userform from another userform? I am using userforms for data entry and would like the option to open a second form whilst entering data into a form Can anyone help? [excel 2003] Just Show it. Call is like this... UserForm2.Show or like this... UserForm2.Show vbModeless depending on whether you want UserForm2 to be modal or non-modal. Remember to change my example UserForm name from UserForm2 to whatever the name your second UserForm is. -- Rick (MVP - Excel) "Roger on Excel" <RogeronExcel@discussions.microsof...

change font of all data labels at once
I have excel 2003. How do you change the font/font size of all the labels at one time? I can click on each one and do them one at a time but surely there's a way to do them all at once? :) Thanks Hi, You should be able to select all the data labels from 1 series at a time and apply formatting. Either you can click a label twice which in effect selects that individual data label or you have multiple series each containing 1 data point. Cheers Andy Meenie wrote: > I have excel 2003. How do you change the font/font size of all the labels at > one time? I can click on each one...

Data>Validation Question
Hello and greetings from South Texas (very cold). I've created a simple spreadsheet report with six different categories (columns). In two of those categories I implemented drop down lists using Data>Validation>List, etc. Initially it worked like it was supposed to therefore giving me the luxury of selecting different options from the drop down menu. I locked several cells (not including the two columns) and protected the worksheet with a password. As I opened the file this morning I deleted the current information in an attempt to enter new information (expecting the dro...

Data types?
Hi I'm trying to sum a set of cells using =SUM(Grid!D3+Grid!H3+Grid!L3...Grid!CR3) and I get an error message saying 'A value used in the formula is of the wrong data type'. What does this mean?? -- Derek Norman ------------------------------------------------------------------------ Derek Norman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26004 View this thread: http://www.excelforum.com/showthread.php?threadid=393659 Hi Derek, The error message suggests that you are using your formula in VBA (???) If that's true, then there are a few ...

passwords - userforms
[Excel 2003] I have a spreadsheet which uses forms to enter and display data. The Spreadsheet uses a main page form "frmDashboard" from which other userforms are activated using command buttons. The form frmDashoboard remains open in the background as a desktop while the user activates the other forms. I am looking at various methods to password protect the opening of other forms from the main useform frmDashboard and I use the following code to do this: Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password&q...

copyinf a file with more then one data stream
downloaded an application programme from the vendor's site as a zip file. When attempting to copy it in a flash memory stick a warning pops up saying that that zip file contains more then one data stream but the destination volume does not support this feature. Some data will not be preserved as a result. What are these more then one data streams and will the destination file become unusable if I proceed with copying? aa wrote: > downloaded an application programme from the vendor's site as a zip file. > When attempting to copy it in a flash memory stick a warning...

Formatting of Data Labels in Histograms
I have used histograms extensively in MS2003 but have encountered a broblem using MS2007. I have a spreadsheet that shows dollar amounts of contracts being awarded during certain months over a three year period. Some months more than one contract is awarded, others have one or none. Therefore, my histogram is a stacked column type. My X-axis shows months and my Y-axis dollars. The problem I am encountering is with the data labels which I put centered on the column. The dollar amounts show correctly but for some reason there is a zero at each interval of the X-axis, even for months...

How to merge rows of data in 2 columns into 1 col.
I have 199 rolws of data in 2 cols and want to merge it into one col. for printing lables. Excels instructions tell only how to merge 1 row, 2 cols into 1 col. How do I do this? =A1&" "&B1 copy down, then copy and paste special as values in place -- Regards, Peo Sjoblom (No private emails please) "Pianoman" <Pianoman@discussions.microsoft.com> wrote in message news:6B00B84E-04EB-458F-9641-0126C7192E33@microsoft.com... >I have 199 rolws of data in 2 cols and want to merge it into one col. for > printing lables. Excels instructions tell only ...

Ranking Data
Hello, any help would be appreciated! I have a sheet with four different data sets which I would like to rank, here is the problem. Each data set consists of two columns, one column is an ID number and the other column is an associated numerical score, these need to be linked. This is the same for each of the four sets of two columns. Then, the numerical score for each data set needs to be multiplied by a factor, creating another numerical value, linked to the ID number. Then each of these numerical values, linked to their ID number will be sumed to created an overall score for each ID nu...

Is it possible to use 'grid' as a value in vb to process XL data?
I was processing the Excel data that has 'grid' as a value, When I used the VB macro to process that particular row was missing. When I renamed this to 'grid1' the result appears with 'grid1'. Is there any reason for this? Philip ...

Data archive with GP Manufacturing
Has anyone had experience with any data archive with GP Manufacturing. The product from Professional Advantage does not support Manufacturing archive, at least not in the documentation. Don't have an answer for you but we will be doing the same in the next week or so. Will report back when done. "DavidInterDyn" wrote: > Has anyone had experience with any data archive with GP Manufacturing. The > product from Professional Advantage does not support Manufacturing archive, > at least not in the documentation. ...

Olap data views
Hello, I've deployed a farm solution with SQL Server 2005, Project server 2007 and windows Server 2003 SP2. I configured Project Server 2007 default cube sucsessfuly and I can create views and see them in PWA Data Analysis. I created a new project Data Analysis Services in Visual Studio. I deployed it remotely and I can see the New cube deployed if I connect to Analysis Services SQL Services Management Studio. Problem start in PWA, because i can't see the newly deployed cube. I only see the default cube created in pwa interface if i go to "server settings...

Extracting Data
Hi really hope someone can help I have been sent a Word documents 250 pages long and which contains addresses. I need to get these into either Excel or Access. Each address is 4 lines and there is 2 or 3 line feeds between each address. Is there anyway I can extra this data into and Excel worksheet or Access table? Very many thanks Alex On Sun, 28 Mar 2010 16:50:41 +0100, Alex Hammerstein <aph@misnet.co.uk> wrote: >Hi really hope someone can help > >I have been sent a Word documents 250 pages long and which contains >addresses. I need to get thes...

userform
hello everybody, is it possible to see the content of a cell (i.e. worksheet 1 cell y32) (and all it's changes) in a userform (which is also used to input data) while it is still open? Hi You will probably need some code for that, like Label1.Caption = Sheets(1).Range("Y32").Text trigged by some relevant events, like the Exit events of your input boxes. -- HTH. Best wishes Harald Followup to newsgroup only please. "Mauro" <mlauro@fastwebnet.it> wrote in message news:WlKQb.4922$VS6.4580@tornado.fastwebnet.it... > hello everybody, is it possible to see the ...

Plotting data in 3D properly
I'm wanting to make a bar chart that compares frequency, voltage drop and duration, and my boss insists it can be done with standard Excel charts, but I'm stumped. What he wants is, taking Y as being up, the bars height up Y to represent frequency, how far the bar is along X to represent the duration and how far the bar is along Z to represent the voltage drop. I can only find bubble charts to compare 3 sets of data, but they look rediculous. My other solution was to make a new series for EVERY voltage drop, but that is tedious to do and difficult to put into a macro (I'm l...

Excel VBA
Hi Excel VBA'ers, Via Google I found some great code (see below) that enables MouseDown to paste what's in clipboard. I want this code to apply to ALL of the textboxes in my Userform. Instead of repeating this code multiple times for every textbox, I was wondering how/if I can specify multiple textboxes using the one instance of the code please. (Hmm, if I'm on the right track, perhaps I have to learn about Class Modules to do this??) Here is the code that I found: Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Sing...

Finding the Cell Reference for the minimum value of a data list.
I am trying to figure out how to automatically refernce a location of a cell (ex. C5)which has been calculated to be the minimum value of a set of data. If I can reference the row of this cell absolutely, then I want to use that value as the beginning data reference point for both the X and Y data values of an XY scatter plot. You can reference it using the MATCH function with INDEX or OFFSET: I.e., the starting cell might be referenced as: INDEX(C:C, MATCH(MIN(C:C),C:C, 0)) while the range to plot could be referenced as: OFFSET(C1,MATCH(MIN(C:C),C:C, 0)-1,0,numrows,numcol...

If data present, then display graph.
Is it possible for one to display graphs based on whether data is present in particular cells? For instance, I wish to display the information of cells A1 to A8. A9 is blank. I create a graph to reflect the information of cells A1 to A8. I now fill in information in cell A9. I now wish to display the information of cells A1 to A9. Is it possible for me to create a formula for the graph to automatically represent data from cell A9 when data is present and automatically exclude it when data is not present? Hi, You can create a dynamic graph based on defined names. http:/...

Forms and Input Data
I created a report form on Sheet 1 and a set of input data on subsequen Sheets 2, 3, ... link to the report. The report is to show informatio on either of the data I select. Are there simple steps for me to pick either set of data I want t display on the report? I've thought of a pull-down menu so that if select "1", the report will show data set "1", and so on. Yet I don' know how to do it. I tried the "replace" command by highlighting al the cell in the Report sheet then type "2" in place of "1" to replac the data set then hitting...