none
Excel VB: eg for, can i "Set" an executable command in a Set Line .. RRS feed

  • Question

  • 161120:  Excel VB:  eg for,  can i "Set" an executable command in a Set Line ..

    hi..  not that big of a problem/ not urgent..  maybe just a syntax thing?

    PROBLEM 1:  small question:  can i set an open the "replace" dialog..  in  a set line eg (this in error):

        Dim openRD As String   'x    'how to dim a process / executable
        openRD = application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN REPLACE DIALOG

    (i can run the open 'dialog' easily enough by just making another sub with the same command line as same name:  openRD    ....  update to finish sentence:      openRD (replace dialog);  can i make a dim-set for this, instead of the long commandbar line ? each time i wan to use it.)

    .

    PROBLEM 2:  incidently is there a VB to open the options portion (prefer as always expanded).  i was only one who could come up with a 'work around' answer.  would rather not have to use sendkeys..   this eg just uses a work cell, if it not equal ".x" then do a send keys:  alt-T  as below

    .

    Sub RR1()   'REPLACE DIALOG  Reset Replace dialog & open (with presets)
        'Dim openRD As String   'x    'how to dim a process / executable
        'openRD = application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN REPLACE DIALOG
        On Error Resume Next    'just in case there is no active cell
        On Error GoTo 0         'on off need:
        'Application.FindFormat.Clear                  'yes works, might not want
        'Application.ReplaceFormat.Clear               'yes works
        
        Cells.find what:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext   ', MatchCase:=True, SearchFormat:=False
        Cells.Replace what:="", Replacement:="", ReplaceFormat:=False, MatchCase:=True  ', SearchFormat:=False  'case works, not matter in find if changed in replace
        
    'REPLACE DIALOG:  (need each in separate instance for sendkeys to happen)
        'application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN YES WORKS,easier to manipulate  NEED: SHOW OPTIONS
        
    'MAGIC TRICK:  workaround for have toggle view to open position, automatically, since not able to find vb setting.
        If FileOpen("myfile") Then    'If BookOpen("myfile") Then
            If RANGE("A3").Value = ".x" Or Workbooks("myfile.xls").Sheets("taba").RANGE("A3").Value = ".x" Then
              'openRD
              application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN REPLACE DIALOG
              RANGE("A3").Value = ".x": Workbooks("myfile.xls").Sheets("taba").RANGE("A3").Value = ".x"
            Else
              application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN REPLACE DIALOG
              SendKeys ("%{T}"): RANGE("A3").Value = ".x": Workbooks("myfile.xls").Sheets("taba").RANGE("A3").Value = ".x"
            End If
            
        Else
            If RANGE("A3").Value = ".x" Then  'nothing
    'rr1() RR1()
            Else
              application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN REPLACE DIALOG
              SendKeys ("%{T}"): RANGE("A3").Value = ".x"
            End If
        End If
        
          'MsgBox "YES" & Space(10), vbQuestion    ', "title"    & vbCr &
    'other ck:
        'Application.CommandBars("Edit").Controls("Find...").Execute
        'Application.Dialogs(xlDialogFormulaFind).Show    'will search whole book by default.
        application.EnableEvents = True               'EVENTS
    End Sub











    • Edited by Davexx Wednesday, November 23, 2016 12:59 PM
    Sunday, November 20, 2016 9:43 AM

Answers

  • Hi,

    We could not set the properties of the Replace Dialog.

    I check the ID number for CommandBars in List of ID numbers for built-in CommandBar controls in Excel 2000 and IdMso from 2007 Office System Document: Lists of Control IDs

    I think there is no other workaround to expand the "Option" programmatically. Office doesn’t expose any IdMso or ID number for this "Option" button.

    Besides, from Office 2007, it no long uses CommandBars. The use of CommandBars in some Microsoft Office applications has been superseded by the new ribbon component of the Microsoft Office Fluent user interface.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Davexx Monday, November 28, 2016 3:31 PM
    Monday, November 21, 2016 6:47 AM
    Moderator
  • Hi Davexx,

    >> i can run the open 'dialog' easily enough by just making another sub with the same command line as same name:  openRD

    In my option, you could not set Execute function to a variable. If you want to use goRD directly next for the same function “application.CommandBars("Edit").Controls("Replace...").Execute   ”. I would suggest you try something like below.

    Sub TEST1()
    goRD
    End Sub
    Function goRD()
    application.CommandBars("Edit").Controls("Replace...").Execute   
    End Function

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Davexx Monday, November 28, 2016 3:30 PM
    Monday, November 28, 2016 7:08 AM

All replies

  • Hi,

    We could not set the properties of the Replace Dialog.

    I check the ID number for CommandBars in List of ID numbers for built-in CommandBar controls in Excel 2000 and IdMso from 2007 Office System Document: Lists of Control IDs

    I think there is no other workaround to expand the "Option" programmatically. Office doesn’t expose any IdMso or ID number for this "Option" button.

    Besides, from Office 2007, it no long uses CommandBars. The use of CommandBars in some Microsoft Office applications has been superseded by the new ribbon component of the Microsoft Office Fluent user interface.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Davexx Monday, November 28, 2016 3:31 PM
    Monday, November 21, 2016 6:47 AM
    Moderator
  • hi,  thanks for the help.  i saw i did not properly pose a question for item 1.  maybe just a small thing but might help to know,  for eg:  if i have a command bar line (any task) i know i can run it in a separate sub & then just type something like:  goRD  for replace dialog.

    i have not been able to find an example to code it right in the main vb where happen to be at.  as eg'd in top post..  and the question should have read:

    (i can run the open 'dialog' easily enough by just making another sub with the same command line as same name:  openRD    ....  update to finish sentence:      goRD (replace dialog);  can i make a dim-set for this, instead of the long commandbar line ? each time i wan to use it.)

    the following did not work:

        Dim goRD As String   'x    'how to dim a process / executable
        goRD = application.CommandBars("Edit").Controls("Replace...").Execute   'MAIN REPLACE DIALOG

    .

        goRD      'err:  .Execute   expected function or variable  in: goRD = line above

    Wednesday, November 23, 2016 1:11 PM
  • Hi Davexx,

    >> i can run the open 'dialog' easily enough by just making another sub with the same command line as same name:  openRD

    In my option, you could not set Execute function to a variable. If you want to use goRD directly next for the same function “application.CommandBars("Edit").Controls("Replace...").Execute   ”. I would suggest you try something like below.

    Sub TEST1()
    goRD
    End Sub
    Function goRD()
    application.CommandBars("Edit").Controls("Replace...").Execute   
    End Function

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Davexx Monday, November 28, 2016 3:30 PM
    Monday, November 28, 2016 7:08 AM
  • hi,  thanks.  guesse that may have turned out to be a funny question.  if am right both will work for call as a sub or a function.  trying to recall..  but think i might have been trying find information on other things trying to do..  looks like that will work.
    Monday, November 28, 2016 3:30 PM