I have a checkbox in one workbook that should control the value in another workbook.
In "Format Control /Cell Link" I now have something like this: '[workbookname.xlsm]SheetName'!$T$42
I need the 'workbookname' to be variable..., let's say in a RangeName "Sh". For a sheet-formula this is easy:
=INDIRECT("'[" & Sh &"]SheetName'!$T$42")
But how do I do this for a checkbox and a spinner, I can't use a formula there...
Sheet ref in checkbox?
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sheet ref in checkbox?
What a weird requirement. I don't think that is possible. You'll have to use VBA code in the macro assigned to the control to change the value of the cell you want.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Sheet ref in checkbox?
"What a weird requirement"
I agree Hans. The issue is that I'm trying to interact with a workbook but normally that workbook is locked and the controls are in different sheets.
So I created another workbook where I used formula's to link to the relevant information and presented results in a different and better way.
To tune and optimize, I've also used links to the different controls in the original and put all these links together in sort of a dashboard in the new workbook.
The end result is that without touching the original, I can now use the new WB to tune settings in the original and then display results in a better way in the new WB. Makes sense? ;-)
I agree Hans. The issue is that I'm trying to interact with a workbook but normally that workbook is locked and the controls are in different sheets.
So I created another workbook where I used formula's to link to the relevant information and presented results in a different and better way.
To tune and optimize, I've also used links to the different controls in the original and put all these links together in sort of a dashboard in the new workbook.
The end result is that without touching the original, I can now use the new WB to tune settings in the original and then display results in a better way in the new WB. Makes sense? ;-)
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Sheet ref in checkbox?
The ".LinkedCell" accepts a cell (text); so e.g.
.LinkedCell="A1"
I need to write this cell: Workbooks(WBName).Worksheets(ShName).Range("A1")
So how do I put that in the .LinkedCell property then?
.LinkedCell="A1"
I need to write this cell: Workbooks(WBName).Worksheets(ShName).Range("A1")
So how do I put that in the .LinkedCell property then?
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sheet ref in checkbox?
I don't think that is possible. (is there an echo in here?)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Sheet ref in checkbox?
Sorry Hans; you said above "You'll have to use VBA code in the macro assigned to the control to change the value of the cell you want".
I interpreted that as "it can't be done in Excel but it is possible in VBA", hence my attempt to now use VBA and an "ActiveX Controls" spinner instead of a "Form Controls" spinner and use VBA to populate the LinkedCell property.
I guess all that is left now (and that is what you probably meant) is to read a local spinner value and use VBA to write that value into the right cell of the original sheet... I can make that happen. ;-) Thanks for helping me find the right track.
I interpreted that as "it can't be done in Excel but it is possible in VBA", hence my attempt to now use VBA and an "ActiveX Controls" spinner instead of a "Form Controls" spinner and use VBA to populate the LinkedCell property.
I guess all that is left now (and that is what you probably meant) is to read a local spinner value and use VBA to write that value into the right cell of the original sheet... I can make that happen. ;-) Thanks for helping me find the right track.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands