Button to change worksheet reference

[JimDandy]

I’d like to have two worksheets with similar functions and a third that has cell references to one worksheet or the other at a time by using either a set of radio buttons, a toggle button, or even two separate buttons with the names of the desired source on the face of the button.

The worksheet names are “New Financing” and “Assumption”.

Can someone assist with the creation of either of these options?

[Leith Ross]

Hello JimDandy,

You should post a copy of your workbook. This will provide all the information needed to develop and test any solutions for you.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

[JimDandy]

Thank you, attached is a very simple example of what I'm trying to accomplish. In the example, I used the data in cell A1 and the INDIRECT function to accomplish what I'm after but I'd prefer (if possible) not to use a reference to A1 like this.

I'd prefer to use code behind the command button to force the formulas to toggle the reference to the two worksheets without anything in cell A1 as well as to change the text on the command button to match.

[Leith Ross]

Hello JimDandy,

The macro below has been added to the attached workbook. This replaces the worksheet name in the formula with the new worksheet name assigned to the Command Button.

Please Login or Register  to view this content.

[JimDandy]

Thank you for the reply, I receive the error below on the line RegExp.Patern = WksName:

Run-time error '424':
Object Required

[JimDandy]

Ok, I believe that I needed to add a couple additional lines. By adding the following two before RegExp line, it seems to have corrected the error I was having.

Dim RegExp As Object
Set RegExp = CreateObject("VBScript.RegExp")

[Leith Ross]

Hello JimDandy,

The Regular Expression should have been removed before I posted. Turns out I didn't need to use it. The code below should work without any problem.

Please Login or Register  to view this content.

[JimDandy]

Indeed it does...this has helped immeasurably...

Many Thanks!