Posts by Jack in the UK

  • Unwanted "beeps" during macro when it minimizes the Adobe window

    Something I played around with years and years ago - kind of got it working, sorry I can not locate the xls I have all the testing in


    jiuk


  • Unwanted "beeps" during macro when it minimizes the Adobe window

    This gets a little complicated, the send keys here

    Code
    1. SendKeys ("%n"), True

    Use to close Adobe, or minimize (something like that), not sure why this code causes a bleep sound


    Can you double check this is correct


    jiuk

  • Run-Time Error 5854 String parametere too long

    Try this code and see if it behaves, if so the find is big criteria, generally I hit one unique word


    Try find subsidies only


    jiuk

  • Move sheets to master sheet

    Hi JackWhittaker, welcome to OzGrid good to have you along


    Just a quick one can you change the subject title to something more meaningful that reflects your question, help the member a lot more and you getting help


    Also please read the forum rules, you will see a link in my sign, many thanks in advance



    jiuk

  • User Form adding adding rows starting from below table and checkbox not clearing

    You need to untick the checkbox at the end of its use line 19 is the check and code is to line 26


    So many add a forced change statement to change the checkbox value = FALSE


    Code
    1. UserForm1.CheckBox1.Value = False


    This code you can play and test before adding to your work, test on a separate spreadsheet first


    Code
    1. If UserForm1.CheckBox1.Value = True Then
    2.        MsgBox "Checked", , "Jack in the UK"
    3.     Else
    4.        MsgBox " UnChecked", , "Jack in the UK"
    5.     End If
    7. UserForm1.CheckBox1.Value = False

    jiuk

  • blinking different color in label on userform

    This should cover what you want, again test in a copy of your work or a new spreadsheet just in case


    This gives the best of 'all' worrlds. Not the greatest code but should be robust and work ok


    jiuk


  • blinking different color in label on userform

    This sould more or less do what you want, maybe test on a copy of your work forst and add this code to the UserForm module code, add a command button1


  • VBA Creating new sheets and moving code

    Close ‘only’ the VBA Editor and re-open it using Alt + F11 Key
    See if the issues is correct or not.


    If this issue persists:
    Save the Workbook
    Close the Workbook
    Re-open open the workbook
    Check in the VBA Editor if things are corrected or not


    Maybe a workbook re-name failed and not updated in VBA Editor


    If the issues also persists:
    Make a copy of the data of that sheet and paste on a new sheet. This will be same for any codes, remember to copy to the same places


    Re-name that problematic sheet as …. OLD and rename the new sheet as old one was


    jiuk

  • change a macro button from single click to double click

    If you use this code to a Command Button, things should work



    Code
    1. Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    4.     msgbox "Jack your double clicked Command Button 1"
    7. End Sub



    Now if you want to derail single click of Command Button, just don’t have any code assigned to single click and nothing will happen or for better coding try


    Code
    1. Private Sub CommandButton1_Click()
    4.     ‘// jiuk – do nothing
    7. Exit sub
    10. End Sub

    Jiuk

  • Automatically Save an Excel spreadsheet

    These sorts of codes are great as backups, were all guilty on not saving enough


    This is save the active workbook, assumes already named and saved, and really only replaces using CTRL+S keys pressed together on the keyboard


    Admitted there are in this code as is common parameters to that save, thou again that exposes rick


    Learn to use CTRL+S always and as you work, no matter what safety measures you have in place


    Just a point, nothing more


    jiuk

  • How do i extract values in a String?

    Quote from Justin Doward

    Just found that if you insert

    Code
    1. On Error Resume Next

    in the code for each of the UDFs I think it fixes the problem if same values are found.

    option explicit is not set as noted in the code - could cause an issue or two as things not declared fully/correctly


    Make sure you correctly turn on error back 'on', what that code does is ignore errors


    jiuk

  • Set Column Width - question about shg code February 2008

    A little bit fuller code, sorry Jack had to write this one up for you


    jiuk


  • Difference between two dates should give result in years, months and days

    If you add this code into a standard VBA module and run things will work as you want, this is not part of the spreadsheet attached


    Add a command button, copy the code to a module, attach the code macro to the command button and save


    Run the code. If you stuck on any part of this, please have try and post back for assistance, be happy to help


    Jack has added a message box to show the output as you require


    If you want to have text boxes and run the codes once input your need trigger and a far bit of validation codes - maybe the best approach is to have a second form and rebuild from there step by step, the code works so it is useable, thou will need re-jigging quite a bit


    jiuk