none
VB: combine commands for 2 vb actions, into 1 simultaneous action RRS feed

  • Question

  • hi,  i am trying to figure out how to combine commands into one action for:  Scrollrow & cell.activate

    I would like to combine the two items below to allow 1 seamless action.  i have been looking for any commands that might do this.  not sure if a UDF is required, but there must be some way to join the commands.  i do not wish to use:  ScreenUpdating = False    for the screen disrupt.  the answer to this will serve multiple uses.

    some things tried / trying / looking at:  JOIN,  GOTO,  UNION,  trying to combine DIMS & SETS,  UDF's ...

    i know you combine some simple items like RANGE().select  &  SELECTION.copy  into just:  RANGE.copy,  :)  so in a word problem eg:

        Dim arG1 as ?,  arG2 as ?:  arG3 as ?:  set arG3 = (arG1 & arG2)

          'ActiveWindow.ScrollRow = namX.row - 1    'arg1
         ' namX.Select    'arg2

        RUN arG3

    AS IT STANDS:  item 1 performs then there is pause while item 2 catches up.    thanks in advance.

    i can give more notes on what i have for (rough) ideas & can post afterwards, a working use copy.  for this example i have:

    sub test()

        Dim E7 As String: E7 = RANGE("E7")    'workcells show eg:  BH100:BH500    &    BH16
        Dim G7 As String: G7 = RANGE("G7")    'formula eg:  see below 

        Dim namX As RANGE: Set namX = RANGE(E7).find(what:=RANGE(G7).Value, LookAt:=xlWhole)

        If Not namX Is Nothing Then   

          ActiveWindow.ScrollRow = namX.row - 1    'ITEM 1
          namX.Select    'ITEM 2

        end if

    end sub

    workcell eg: 

    =SUBSTITUTE(SUBSTITUTE(CELL("address",$BH$100),"$",""),"","")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$BH$500),"$",""),"","")




    • Edited by Davexx Sunday, November 27, 2016 10:48 PM
    Sunday, November 27, 2016 10:23 PM

All replies

  • Hi Davexx,

    you had asked that,"how to combine commands into one action for:  Scrollrow & cell.activate".

    using & you can only  concatenate .

    but it will not work to combine both commands and execute like a single command.

    it is use to concatenate the string or values.

    so here you are miss understanding the use of this operators.

    there is no way to combine two different commands in to single command and execute together as they are one single command.

    they will execute one after another.

    like first it will scroll the row and then it will activate the cell.

    there are break and combining statements in vba that use to break and combine the statements. but it will not work as you desired. they also execute step by step.

    like if the statement is too long then we can use "_" (underscore) to break it so that we can see it in multi lines easily.

    example:

      cmd.CommandText = _
            "SELECT * FROM Titles JOIN Publishers " _
            & "ON Publishers.PubId = Titles.PubID " _
            & "WHERE Publishers.State = 'CA'"
    

    to combine we can use ":" (colon).

    Public Const cdbArea = 1: Public Const cdbDist = 2: Public Const cdbChange1 = 4

    but still it will execute independently. it will not execute as a single command.

    it is just use to see, read, understand the code properly.

    so there is no way to achieve your requirement.

    hope by this suggestion you got the answer for your question.

    Regards

    Deepak 


    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.

    Monday, November 28, 2016 2:47 AM
    Moderator
  • thanks!!  (just something i've been trying to fix.   was working on a function i had help with:  for a "QLINK"  alternative to hyperlink using scrollrow instead of offset.  i see other items that can be combined in vb for 1 action,  for eg pick a path AND .calc .select .copy  etc.  hyperlink seems to perform both goto destination with cursor ending there at he same time,  so its seems like it can be done.

    .

    if you can see an quick fixes for what i am using below.  the 2 problems i see are:

      - cannot get external formula to work on QLINK eg:  =QLINK(IF($AD200=1,A$200,A$300),"X")    'gets an error,  can supply ext formula eg but is like:  if row > row, then 9 8 7..

      - cannot spread QLINK over a merged cell without having to doubleclick QLINK to make it work. i got a single click to work with normal =QLINK(A$200,"name")

    .

    Function QLINK(rngToSel As RANGE, strFriendlyText As String) 'add to a module,  see sheet1 srch: qlink()
        QLINK = strFriendlyText
    End Function    'for:  =QLINK($A$200,"A")  or  =QLINK($AD$200:$AF$200,"Friendly Name")

    '

    QLINK VB:    (UPDATE:  not sure i needed double click at all for the basic qlink example,  i have lined doubleclick out..)

    .

    'option explicit
    'Private Sub Worksheet_SelectionChange(ByVal Target As RANGE)  'single click
    ''Private Sub Worksheet_BeforeDoubleClick(ByVal Target As RANGE, Cancel As Boolean)  'dbl click
      'note: can have copies in both places, so when turn macros/ single click option off, double click still works. (by a1 val > 0 below)
     
    'With Target
    '    If .Count > 1 Then: Application.EnableEvents = True: Exit Sub    'EVENTS
    'If RANGE("A1").Value > 0 Then      'easy safety macro's on/off,  do not use with doubleclick version
        
        Dim strFormula As String, strRefToSel As String   'concept-vb:  davexx,  main vb:  rich007
        Dim rngToSel As RANGE
        On Error Resume Next
        strFormula = Target.Formula
        'quick link:  single click scroll row to destination for top of view, moving up -or- dn (esp. in header).
        'no more offsets
        
        If UCase(Left(strFormula, 6)) = UCase("=QLINK") Then    'as:  =QLink($A$897,"friendly name")
            strRefToSel = Mid(strFormula, 8)
            strRefToSel = Left(strRefToSel, InStr(strRefToSel, ",") - 1)
            Set rngToSel = RANGE(strRefToSel)         'remove:  Me. to keep from selecting destination up front & rem update off.
            'Set rngToSel = Me.RANGE(strRefToSel)     'orig
            
            'If ActiveCell.MergeCells = True Then      'PROBLEM:  does not fix merged cell need to double click then goes to destination
            '  application.DoubleClick ': application.DoubleClick
            'End If
     'qlink() QLINK()      
              'application.DoubleClick                           'single click answer ?  maybe no efffect on single click
           
            If Not rngToSel Is Nothing Then
                ActiveWindow.ScrollRow = rngToSel.row - 1       '<< SCROLL  yes   'orig concept
                'rngToSel.Activate           'not in use
            End If
        End If
        'Application.Goto rngToSel   '<< PROBLEM: make this line take affect same time as scrollrow test
        
    'end if
    'end with
    'end sub







    • Edited by Davexx Monday, November 28, 2016 5:47 PM
    Monday, November 28, 2016 5:08 PM
  • hi,  update.  note my vb skill are small & there might be some obvious mistakes you can correct me on.  (the intended use of doubleclick in this example may of had no effect all)

    i have found a way to illustrate this example,  if isolating issues..

    the QLINK seems to be rather quick anyways, but i will need to review other uses of the scrollrow that seem to be much slower.  namely a utility for:  Find Name in a column - scroll row - activate..  where cursor takes a second to arrive at destination

    '

    NOTE  summary next 7? lines,  using change to a dim / set item has changed the affect (of cursor arriving at destination) to be simultaneous,  or almost simultaneous.

    even so i made an alteration to this example,  focuses on a "SET" item being changed from / to .activate:

    EXAMPLE:   by just changing the: IF 1 THEN  to  IF 0,  you will switch between the examples.  one using .activate in the SET = x.activate, the other with the x.activate down in the main IF NOT NOTHING THEN area.  see results:  (in a large - heavy conditional formatting file),  for:

     - Set rngToSel = RANGE(strRefToSel).Activate    'the cursor arrives to destination before the conditional formatting finishes refreshing.

     - using the alternative 2 lines below,  the cursor arrives after the screen refreshes.  in some circumstances a full 1 second delay.

                ActiveWindow.ScrollRow = rngToSel.row - 1       '<< SCROLL  yes
                rngToSel.Activate           'not in use
    .

    .

    change the:  IF 1 to a 0 below, to switch between eg's

    '

    'option explicit
    'Private Sub Worksheet_SelectionChange(ByVal Target As RANGE)  'single click
    ''Private Sub Worksheet_BeforeDoubleClick(ByVal Target As RANGE, Cancel As Boolean)  'dbl click
      'note: can have copies in both places, so when turn macros/ single click option off, double click still works. (by a1 val > 0 below)
     
    'With Target
    '    If .Count > 1 Then: Application.EnableEvents = True: Exit Sub    'EVENTS
    'If RANGE("A1").Value > 0 Then      'easy safety macro's on/off,  do not use with doubleclick version
        
        Dim strFormula As String, strRefToSel As String   'concept-vb:  davexx,  main vb:  rich007
        Dim rngToSel As RANGE
        On Error Resume Next
        strFormula = Target.Formula
        'quick link:  single click scroll row to destination for top of view, moving up -or- dn (esp. in header).
        'no more offsets
        
        If UCase(Left(strFormula, 6)) = UCase("=QLINK") Then    'as:  =QLink($A$897,"friendly name")
            strRefToSel = Mid(strFormula, 8)
            strRefToSel = Left(strRefToSel, InStr(strRefToSel, ",") - 1)
            
    If 1 Then    'CHANGE TO:    IF 0
            Set rngToSel = RANGE(strRefToSel)       
            If Not rngToSel Is Nothing Then       
                ActiveWindow.ScrollRow = rngToSel.row - 1       '<< SCROLL
                rngToSel.Activate           '<<  SLOWER  >>
            End If       
    Else
            Set rngToSel = RANGE(strRefToSel).Activate        '<<  FASTER  but scrollrow not working right now  >>  cursor beats refresh
            If Not rngToSel Is Nothing Then                                        'to destination
                ActiveWindow.ScrollRow = rngToSel.row - 1       '<< SCROLL
                'rngToSel.Activate           'not in use
            End If
        
    End If
        End If
       
    'end if
    'end with
    'end sub




    • Edited by Davexx Monday, November 28, 2016 6:54 PM
    Monday, November 28, 2016 6:20 PM
  • Hi Davexx,

    I can see that you had post a huge description with different lines of code.

    you are saying that some are working and some are not working.

    I think you can start with Excel object model and try to understand the model.

    so that you can decide and select proper properties, functions and events to perform the particular task.

    here it looks like you find some methods to perform some task and you want that it work as you want.

    but it is possible that it designs as to work particular task in particular condition and situation.

    so it maybe not the correct choice to use it to get desired result.

    here below is a link for the object model you can try to visit.

    Excel Object Model Overview

    it contains the detailed information regarding properties, events and methods.

    if you have any further question then let me know about that. I will try to give you further suggestions.

    Regards

    Deepak


    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.

    Tuesday, November 29, 2016 5:22 AM
    Moderator
  • hi,  thaks for the posts..

    sorry for the delay on check some posts.  i ran into some problems,  running a bit slow, tried doing 3 or 5 things at once and a crash :)  hate to post just that for now but will get back as can..

    if is any help to anyone in meantime,  things work on are to cut work times in half to increase productivity,  if any interest vb eg for pasting formulas - formats or all, and New:  calculating those 'selected columns automatically, to include NEW:  calculate other desired columns (in reference to same).  eg at:

    note:  automating pasting columns saving an extreme amount of time.  8 or 10 hour days down to 5 minutes?  thanks.

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/vb-answer-automatically-paste-formulas-down-a/e609ad57-e27d-450d-9e4c-2c95546aac5b

    Monday, March 27, 2017 9:53 PM
  • Hi Davexx,

    This thread is too old and your issue is not related with this thread.

    you have a new issue.

    I suggest you to create a new thread on Excel for Developers Forum.

    Regards

    Deepak


    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.

    Tuesday, March 28, 2017 5:44 AM
    Moderator