none
VB: Is there a way to shortcut a (repetitive) keystroke sequence for use in a Code Page? RRS feed

  • Question

  • hi..  i am looking for a way to ease repetition in a code page, to make a set of repetetive key strokes automated.

    any alternate methods are welcome..  an example might be to have sendkeys run a macro to do like:

    .

    : delete delete delete delete

    thats:  colon space delete delete..

    going wild? :)  would be willing to tempoarily reassign a key (function or other), just to accomplish the task,  even if have to reboot.  thanks.

    Friday, November 25, 2016 11:04 AM

All replies

  • Hi,

    Do you want to repeat SendKeys like:

    Sub test()

    Call repeat("{DELETE}", 2)

    End Sub

    Function repeat(key As String, val As Integer)

    Do Until i = val

    Application.SendKeys (key)

    i = i + 1

    Loop

    End Function

    You could then assign a shortcut key for the macro.

    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.

    Monday, November 28, 2016 9:33 AM
    Moderator
  • hi..  thanks!  would not have thought of that,  but still had a problem with it.  (it is not that important/ ran into other probs below..  but is this supposed to work in a code page?)

    i have this in a code page:  'i place the cursor here/ after letter g in string & hit alt-3,  but just get a Beep.

        Dim A2 As String|  <CURSOR here & hit alt-3  (just beeps, no action)
        A2 = RANGE("A2")

    .

     what was supposed to happen was to combine the 2 lines.  i applied the following items & then closed - reopened file.

        application.OnKey "%3", "alt3"          'a-3 altFIXDIM  (in:  thisworkbook)


    Sub alt3()    'fixDIM())    '<<  AND MACS IN A MODULE
      Call repeat("{DELETE}", 5)
    End Sub

    Function repeat(key As String, val As Integer)
      Dim i As Integer
      Do Until i = val
        application.SendKeys (key)
      i = i + 1
      Loop
    End Function


    although i have a lot of lines set up  as (1 after the other),  it turns out that most of it is in different form for a1 thru a8 first 8 lines of eg:  dim a1 as string, dim a2 as string.. space line, then the next 8 for a1 =  range("a1").  so the question would need to ask is what is the "NAME" for the disciplin  (if it exists)  for coding "code" / how do i google:  code for making code in excel vba  (i get nothing):  embedding code?  code for modifying code?  :)  anyways  the example working on is like:

    WHAT LOOKING FOR

        Dim A1 As String: A1 = RANGE("A1")
        Dim A2 As String: A2 = RANGE("A2")

    WHAT HAVE,  (i can manually delete the 2nd portions)

        Dim A1 As String
        Dim A2 As String
        Dim A3 As String
        Dim A4 As String
        Dim A5 As String
        Dim A6 As String
        Dim A7 As String
        Dim A8 As String
        
        A1 = RANGE("A1")
        A2 = RANGE("A2")
        A3 = RANGE("A3")
        A4 = RANGE("A4")
        A5 = RANGE("A5")
        A6 = RANGE("A6")
        A7 = RANGE("A7")
        A8 = RANGE("A8")
        
        Dim B1 As String
        Dim B2 As String
        Dim B3 As String
        Dim B4 As String
        Dim B5 As String
        Dim B6 As String
        Dim B7 As String    'TTT1 TBB TSS TOPAA TOPXX TOPXYZ TXYZ
        Dim B8 As String
        
        B1 = RANGE("B1")
        B2 = RANGE("B2")
        B3 = RANGE("B3")
        B4 = RANGE("B4")
        B5 = RANGE("B5")
        B6 = RANGE("B6")
        B7 = RANGE("B7")
        B8 = RANGE("B8")
        
        Dim C1 As String
        Dim C2 As String    'etc  thru maybe Z8,  this is repeated many times thru sheet for each:  x  below..  etc.

    Private Sub Worksheet_SelectionChange(ByVal Target As RANGE)  'single click
    'Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'whats the diff for excel.range
    'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As RANGE, Cancel As Boolean)  'double click
    'Private Sub Worksheet_Change(ByVal Target As excel.RANGE)
    'Private Sub CommandButton1_Click() 2 3 4 ..


    • Edited by Davexx Monday, November 28, 2016 3:18 PM
    Monday, November 28, 2016 3:17 PM
  • >>i place the cursor here/ after letter g in string & hit alt-3,  but just get a Beep.

    It does not work when testing with ALT+3. I think we could not override it and assign the macro.

     

    >>code for making code in excel vba

    I think you want to write code programmatically.

    We could add reference "Microsoft Visual Basics for Applications Extensibility X" firstly, then you could use  AddFromString Method (VBA Add-In Object Model) to add macro text in the module.


    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 7:34 AM
    Moderator
  • hi,  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:52 PM