Sheet ref in checkbox?

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Sheet ref in checkbox?

Post by ErikJan »

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...

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sheet ref in checkbox?

Post by HansV »

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

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Sheet ref in checkbox?

Post by ErikJan »

"What a weird requirement" :hairout:

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? ;-)

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sheet ref in checkbox?

Post by HansV »

I guess so...
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Sheet ref in checkbox?

Post by ErikJan »

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?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sheet ref in checkbox?

Post by HansV »

I don't think that is possible. (is there an echo in here?)
Best wishes,
Hans

User avatar
ErikJan
5StarLounger
Posts: 1185
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Sheet ref in checkbox?

Post by ErikJan »

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.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sheet ref in checkbox?

Post by HansV »

ErikJan wrote:
11 Nov 2020, 12:53
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...
Yes, that is what I meant.
Best wishes,
Hans