Adding Control Box to Excel Worksheet

[youngjedi]

I would like to add a vba driven control box with the available dimension elements to Excel. What is the code to do this??? The subnm function seems inefficient to me. I'd rather select from a drop down box and add a calculate line in the code so when I make the selection the form calculates immediately. If there is a better method I'm all ears.

Thanks

[tomok]

To each his own I suppose. I happen to like the SUBNM formula because using it provides a lot of flexibility and it can give your sheets a common look and feel to selecting dimension elements. If you would rather have your own drop-down the best way is to use Excel data validation on the cell. You can point your validation to a hidden tab in the workbook that contains a list of the dimension elements. In either case, just add code to the OnChange event of the cell where the dimension formula is to do a Worksheet.TM1RECALC1.

[Alan Kirk]

I would like to add a vba driven control box with the available dimension elements to Excel. What is the code to do this??? The subnm function seems inefficient to me. I'd rather select from a drop down box and add a calculate line in the code so when I make the selection the form calculates immediately. If there is a better method I'm all ears.

I'm inclined to agree with Tomok. The other complicating factor is if the element is part of a dimension which has element security on it. At least the SubNm method presents only the elements that the user has access to. (If you think that SubNm is inefficient, try maintaining dynamic lists of elements that any given user has permissions for.)

However if you're prepared to try a little from column A and a little from column B and your main issue is that you want to recalculate upon selection, then rather than having a drop-down you also have the option of using a command button. The button could fire a VBA procedure which calls the E_PICK macro function. (Look under TM1 Macro Functions in the Reference Guide for details.) That will launch the subset editor so that the user can make a selection and you can punch the return value (if there is one) into the selection cell, then recalculate.

[youngjedi]

Wow! Thanks for the quick feedback. If I decide to keep the subnm function in the sheet, how would I add that onchange event to a particular cell without tying it to an object in Excel?

Thanks again

[tomok]

Wow! Thanks for the quick feedback. If I decide to keep the subnm function in the sheet, how would I add that onchange event to a particular cell without tying it to an object in Excel?

Thanks again

Go to Google and type in "tying onchange event to cell in vba". You will see a number of links that cover that very topic.

[nick_leeson]

Personally I think your users would appreciate the vba driven control box better but then its horses for courses. Alan is spot on with the "recalculate upon selection'
I have used E_PICK and is great idea too.

p.s. Bear in mind the vba driven control box won't work at all on Websheets.

[lotsaram]

Bear in mind also that if you use the Worksheet_Change event and trigger TM1RECALC1 or TM1REFRESH on change of the dimension element reference in the target cell then in Excel 2010 this will crash Excel (fine in earlier versions of Excel.)

To avoid this you need something like the following:
If Val(Application.Version) < 14 Then
Application.Run "TM1RECALC1"
Else
Me.Calculate
End If

[youngjedi]

Here is the VBA code for anyone who wants to use it. I leveraged the fact that when the SUBMNM function clears, it puts the cursor in the immediate cell to the right, my SUBNM statement is actually in cell D2!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "E2" Then Me.Calculate
End Sub