Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Display a row of data in a form


Reply
 
Thread Tools Display Modes
  #1  
Old 06-05-2007, 09:46 PM
jermyn9 jermyn9 is offline
Newcomer
 
Join Date: May 2007
Location: Singapore
Posts: 16
Default Display a row of data in a form


Hi

I have a qn on how to display a row of information in a form.

For ex: 4 columns
1.cost centre
2.description
3.divsion
4.category

when i select cost centre, i wish to display the other 3 entries in textboxes in the form.

I tried to use combobox or listbox for the user to select cost centre. Problem is that the number of rows is not fixed. (entries might be added and deleted like a database). Therefore in trying to define rowsource ="A1:A7" where 7 is not a fixed number becomes a problem.

I have a function that returns the last row of entries already. but this returns a digit. I'm wondering if there is another way to declare rowsource. like cell(a,b) is an alternative to range("A6").

any help is much appreciated.

thanks and regards
Jeremy
Reply With Quote
  #2  
Old 06-05-2007, 10:36 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

You can use the Row returned by your function like this:
rowsource ="A1:A" & RowVariable

If you have more issues post the code in question and We can help you more readily. When you post code be sure to use the [vb][/vb] tags.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
  #3  
Old 06-06-2007, 01:54 AM
jermyn9 jermyn9 is offline
Newcomer
 
Join Date: May 2007
Location: Singapore
Posts: 16
Default variable multiple row display in form

Hi Roger.

thanks for the input. the code ran smoothly.

I got another qns thou. I am using the below code to display data according to the Cost Centre selected(in the combo box). It is supposed to be dynamic(i think the word is that is), as in when changes are made in the form, the database also changes. My code works fine, just like to ask if there is a better way to do it. Mine works only when the command button is pressed(in blue), I reload the form data back into the database.

comments are in red.

Code:
Private Sub ComboBox1_Change()
Worksheets("Cost centre").Activate

'displays textbox

TextBox1.Value = Cells(ComboBox1.ListIndex + 2, 4)
TextBox2.Value = Cells(ComboBox1.ListIndex + 2, 3)
TextBox3.Value = Cells(ComboBox1.ListIndex + 2, 2)
TextBox4.Value = Cells(ComboBox1.ListIndex + 2, 1)
'MsgBox "combobox value " & ComboBox1.ListIndex

End Sub

Private Sub CommandButton1_Click()
'changes database
Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 4) = TextBox1.Value
Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 3) = TextBox2.Value
Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 2) = TextBox3.Value
Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 1) = TextBox4.Value
End Sub

another problem is that when i select cost centre, there might be more that one row with the same cost centre, eg

201 ATAT
201 ATBT
202 BUTY

etc

I wish to display both ATAT and ATBT in the form. trouble is the no of rows per cost centre is not fixed. Therefore I would appreciate if someone could point me in the right direction as to how to display variable rows in a form. (use a scroll bar perhaps??)

Once again Many thanks and regards
Jeremy
Reply With Quote
  #4  
Old 06-06-2007, 04:36 AM
MPi MPi is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Dec 2001
Location: Quebec
Posts: 898
Default

If you don't need to distribute your application and if you have the control "Litsview" on your PC (in the list of Components available), you could use it to display all your Cost Centers values, just like a grid.

If you don't have this Listview control, you could also use a simple listbox with multiple columns and show up to 10 columns of each Cost Center.

In both cases, you will have to find a way of "remembering" the original line for each entry. This line could be added in a hidden column of the control or in a dynamic array.

MPi
Reply With Quote
  #5  
Old 06-06-2007, 05:52 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

Ok first question, I would use the TextBox Lost focus event to update that textbox data to the Spreadsheet something like this.
Code:
Private Sub TextBox1_LostFocus() Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 4) = TextBox1.Value End Sub Private Sub TextBox2_LostFocus() Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 3) = TextBox2.Value End Sub Private Sub TextBox3_LostFocus() Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 2) = TextBox3.Value End Sub Private Sub TextBox4_LostFocus() Worksheets("Cost centre").Cells(ComboBox1.ListIndex + 2, 1) = TextBox4.Value End Sub

Now for the second issue if you are wanting to display more rows of data then you may have to redefine your approach.
Since you are displaying the data in textBoxes you would have to add additional textboxes to display additional rows of data.
Another approach would be to eliminate the textboxes and use a MSHFlexGrid.

After rereading your post I am wondering if you are just wanting to add additional information in the ComboBox. If that is what you are talking about then you can combine the costcenter with an additional field for each row so that you can tell which record is selected.

Hope this is helpful.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
  #6  
Old 06-07-2007, 12:39 AM
jermyn9 jermyn9 is offline
Newcomer
 
Join Date: May 2007
Location: Singapore
Posts: 16
Default

Hi guys.

perhaps i should explain myself better.

When I select cost centre(there might be more than one row of data per cost centre) i wish to display the corresponding rows in a form, which when updated would automatically update the main database.

it would work something like the autofilter function in excel. when i select 201for cost centre, only rows with cost centres corressponding to 201 will be shown. Hopefully that is clearer?

I get your point in doing away with the textboxes and using MSHFlexGrid., but am having trouble finding articles on how to use it(i dun really understand the articles i found :P)

someone suggested datagrid (supposed to be easier to use?). do you think that is a better idea?
Reply With Quote
  #7  
Old 06-07-2007, 05:29 AM
MPi MPi is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Dec 2001
Location: Quebec
Posts: 898
Default

For the Flexgrid, it may depend on the version you're running.
On Excel 2000 and earlier versions, I can't use it... I always have an error message when trying to put it on a UserForm... I never tried on 2003. It's a control that comes with VB, not Office.
If you find a way of using it, look for TextMatrix(Row, Column) to read/write data. Look also for Row, Rows, RowSel and MouseRow (same for Col) which are the basics.

For the ListView, I know it works, but every users have to have this control on their machine and be registered in the Registry. Same for the FlexGrid...

If you don't have more than 10 columns, the multi-column listbox would be an easy way of doing it.

MPi
Reply With Quote
  #8  
Old 06-07-2007, 05:02 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

Are you using VBA or VB6?
How do you want to display the data on the form?

From your posts I had thought that you want each row to be modified in the textboxes. Since you have no way of knowing the number of rows in advance you either need a flexible edit control such as a datagrid or you could display the data in some method an allow selection of the row that you want to allow editing.

If you are using VB6 I can help but if using VBA I am more limited since I have not worked with it and don't know all the limitations.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
  #9  
Old 06-12-2007, 02:04 AM
jermyn9 jermyn9 is offline
Newcomer
 
Join Date: May 2007
Location: Singapore
Posts: 16
Default

Hi guys...

sorry for the late reply.

hey MPi
Am now following your advise and reading up on multi-column listbox. Hope this works. Just one qn, does it update the data on the excel sheet?

hi Roger
I am using VBA. it would look something like the autofilter function in excel. when i select 201 for cost centre, only rows with cost centres corressponding to 201 will be shown on the form. probably with a combo box that will seach for the cost centre.

thanks for the input guys
jeremy
Reply With Quote
  #10  
Old 06-12-2007, 03:29 AM
jermyn9 jermyn9 is offline
Newcomer
 
Join Date: May 2007
Location: Singapore
Posts: 16
Default

sorry MPi, just realised that I do need more than 10 columns. Does tt mean I can't use multiple-column listbox?
Reply With Quote
  #11  
Old 06-12-2007, 03:08 PM
MPi MPi is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Dec 2001
Location: Quebec
Posts: 898
Default

It may depend on the version you have, but I think the max is 10 columns (Index 0 to 9).

I'm not sure from which version it's available, but there is another control called "Spreadsheet" which you can find under the name Microsoft Office Spreadsheet in the list of available controls.
I never used it, but it could helpful for your needs...(?)

MPi
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->