Prev: For-next loop question
Next: Run-time 438 - OptionButton troubles
From: Toxter on 20 Feb 2010 13:53
Hi Peter,

Thank you, this solution works well.

"Peter T" wrote:

> Oops, this is not right
> > If Len(v) Then ActiveCell.Formula = v
>
> try again
>
>
> Sub test2()
> Dim bRes As Boolean
> Dim v As Variant
> Dim sFmla As String
>
> If ActiveCell.HasFormula Then
> v = ActiveCell.Formula ' always a String
> Else
> ' data type could be string, double, boolean, error etc
> v = ActiveCell.Value
> End If
>
> bRes = Application.Dialogs(xlDialogFunctionWizard).Show
>
> If bRes Then
> sFmla = ActiveCell.Formula
> If Len(v) Then
> ActiveCell.Formula = v
> Else
> ActiveCell.ClearContents
> End If
> MsgBox sFmla
> Else
> MsgBox "user cancelled"
> End If
>
> End Sub
>
> Regards,
> Peter T
>
>
>
> "Peter T" <peter_t(a)discussions> wrote in message
> news:%231uI7nhsKHA.3800(a)TK2MSFTNGP06.phx.gbl...
> > Try this macro in VBA
> >
> > Sub test2()
> > Dim bRes As Boolean
> > Dim v As Variant
> > Dim sFmla As String
> >
> > If ActiveCell.HasFormula Then
> > v = ActiveCell.Formula ' always a String
> > Else
> > ' data type could be string, double, boolean, error etc
> > v = ActiveCell.Value
> > End If
> >
> > bRes = Application.Dialogs(xlDialogFunctionWizard).Show
> >
> > If bRes Then
> > sFmla = ActiveCell.Formula
> > If Len(v) Then ActiveCell.Formula = v
> > MsgBox sFmla
> > Else
> > MsgBox "user cancelled"
> > End If
> >
> > End Sub
> >
> > Regards,
> > Peter T
> >
> > "Toxter" <Toxter(a)discussions.microsoft.com> wrote in message
> > news:2069A71D-869D-45EE-A706-01F32BC5595E(a)microsoft.com...
> >> Hi Peter, thanks for a quick reply.
> >>
> >> Can you please elaborate what do you mean by "trap the activecell's
> >> formula
> >> to a variant" ?
> >>
> >> Thank you
> >>
> >> "Peter T" wrote:
> >>
> >>> Trap the activecell's formula to a variant
> >>> Show the dialog
> >>> Trap the boolean return value of the dialog (false cancelled, true if
> >>> user
> >>> hit enter)
> >>> If true, read the new formula from the activecell, and possibly it's
> >>> value
> >>> too
> >>> Reinstate the original formula
> >>>
> >>> Regards,
> >>> Peter T
> >>>
> >>>
> >>> "Toxter" <Toxter(a)discussions.microsoft.com> wrote in message
> >>> news:18803047-AD2E-49F9-94A5-4F1076ED580A(a)microsoft.com...
> >>> > Hi All,
> >>> >
> >>> > I'm working on VSTO Excel 2007 AddIn in C#.
> >>> >
> >>> > I need to call Formula Dialog and than to fetch the formula string
> >>> > from
> >>> > that
> >>> > dialog. Problem is that dialog doesn't offer something like
> >>> > dialog.FormulaText but instead inserts formula in ActiveCell. So code
> >>> > like
> >>> > this:
> >>> >
> >>> > var dlg =
> >>> > this.Application.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogFunctionWizard];
> >>> > dlg.Show(missing, missing, missing, missing, missing, missing,
> >>> > missing,
> >>> > missing, missing, missing, missing, missing, missing, missing,
> >>> > missing,
> >>> > missing, missing, missing, missing, missing, missing, missing,
> >>> > missing,
> >>> > missing, missing, missing, missing, missing, missing, missing);
> >>> >
> >>> >
> >>> > So, this code will Show Function Dialog, and it will correctly place
> >>> > generated formula to ActiveCell.
> >>> >
> >>> > Is it possible that I do this without placing value in ActiveCell and
> >>> > get
> >>> > Formula string?
> >>> >
> >>> > Thank you
> >>> >
> >>> >
> >>>
> >>>
> >>> .
> >>>
> >
> >
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: For-next loop question
Next: Run-time 438 - OptionButton troubles