A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Linking data from ComboBoxes on spreadsheet?



 
 
Thread Tools Display Modes
  #1  
Old September 24th, 2009, 08:57 PM posted to microsoft.public.excel.links
MikeG0930
external usenet poster
 
Posts: 3
Default Linking data from ComboBoxes on spreadsheet?

I have several (source) spreadsheets from several people that are saved in
the same folder. These spreadsheets are formatted the same. I have ComboBoxes
in certain cells to help keep data entered uniform for reporting purposes.

I also have a "Master Workbook" that contains a duplicate of each
(Destination) spreadsheet. To update each sheet I used the Paste Link option.
This works fine except it doesn't paste the contents of the ComboBoxes. Is
there a way to correct this? Thanks.
  #2  
Old September 25th, 2009, 02:23 PM posted to microsoft.public.excel.links
MikeG0930
external usenet poster
 
Posts: 3
Default Linking data from ComboBoxes on spreadsheet?

I thought I might have a response by now. Am I in the correct group or should
I be in the programming group for this problem? Sorry for not being patient.

"MikeG0930" wrote:

I have several (source) spreadsheets from several people that are saved in
the same folder. These spreadsheets are formatted the same. I have ComboBoxes
in certain cells to help keep data entered uniform for reporting purposes.

I also have a "Master Workbook" that contains a duplicate of each
(Destination) spreadsheet. To update each sheet I used the Paste Link option.
This works fine except it doesn't paste the contents of the ComboBoxes. Is
there a way to correct this? Thanks.

  #3  
Old September 26th, 2009, 03:08 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Linking data from ComboBoxes on spreadsheet?

I have been on vacation so only just had time to read your message.
All you can link using paste link is one cell to another.

So, the way to achieve what you want is to have the combo box on the
source sheet linked to a cell, say Z99, on the source sheet by setting
its LinkedCell property. Z99 will contain the value in the combobox if
it is a Control Toolbox combo or the ListIndex of the item selected
from the list if it is a Forms toolbar combobox.

Then on the destination sheet you have a similar combo box linked to a
cell, say Y88. And you copy Z99 from the source sheet and paste link
it into Y88 on the destination sheet. Then the destination combo
should show the same as the source combo. However, you would not be
able to change the value of the destination sheet's combo without
overwriting the formula in the linked cell - if you don't need the
combo on the destination sheet to allow selection of a different item
then you should disable it.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #4  
Old September 27th, 2009, 02:34 PM posted to microsoft.public.excel.links
MikeG0930
external usenet poster
 
Posts: 3
Default Linking data from ComboBoxes on spreadsheet?

That was easy! Thank you for your help.
Since I needed the "destination spreadsheet" to update any changes made to
the "source spreadsheet", I used the Paste Link option which works fine now.
Is there any way to get rid of the zeros in the cells that data hasn't been
entered in? Can I do it by code?

"Bill Manville" wrote:

I have been on vacation so only just had time to read your message.
All you can link using paste link is one cell to another.

So, the way to achieve what you want is to have the combo box on the
source sheet linked to a cell, say Z99, on the source sheet by setting
its LinkedCell property. Z99 will contain the value in the combobox if
it is a Control Toolbox combo or the ListIndex of the item selected
from the list if it is a Forms toolbar combobox.

Then on the destination sheet you have a similar combo box linked to a
cell, say Y88. And you copy Z99 from the source sheet and paste link
it into Y88 on the destination sheet. Then the destination combo
should show the same as the source combo. However, you would not be
able to change the value of the destination sheet's combo without
overwriting the formula in the linked cell - if you don't need the
combo on the destination sheet to allow selection of a different item
then you should disable it.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #5  
Old September 29th, 2009, 08:24 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Linking data from ComboBoxes on spreadsheet?

If zero would not occur in the valid data to which you are linking then
you could just use a number format to suppress the appearance of zeros
coming from the empty cells. Select the destination cells and give
them a custom number format such as 0;-0;


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 




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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.