Posts by jl2509

    Hi Carim


    Thanks for the repsonse


    I have tried the above proposal, but do not have anything to add after this to make the commndbutton align next to the activecell.
    Is this possible as I can find nothing in searches.


    btw, is something wrong with the site, I keep getting connection erros and very slow?


    Thank you

    Hi Folks,


    Still building my sheet and yet another question.
    Any thoughts how to align a commandbutton to the left of an active cell?


    The Commandbutton inserts a row above the activecell, but as the sheet is many rows downs, the user would need to scroll up to click the commend button.
    Freezing windows is not an option due the the makeup of the sheet.


    I am currently trying the following but its not ideal as the command button could still be better aligned:


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
            On Error GoTo 0
            With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
                CommandButton2.Top = .Top + 200
                CommandButton2.Left = .Left + 10
            End With
    End Sub


    Thanks

    Hi Mumps


    Thanks for putting up with me on this thread.


    I have put something together that may not look pretty, but using snippets from your macrocs and other sources, I think I have something.



    Updated sheet attached with this implemented


    Great posts and great support.


    Thank you

    Hi Mumps


    Sorry, again but am I overcooking this or just rubbish at explaining..


    The only thing I need to happen when I select an option button is to hide or unhide columns. as per the original sheet supplied. I do not need message boxes or print actions at this point.
    The code I need when clicking the printbutton "commandbutton1" is to check the optionbuttons to see what is selected
    then the correct message box with input options then either print or exit the sub


    Many Thanks

    Hi Mumps,


    I really do appreciate your efforts. I thought attached sheet earlier in the thread contained sufdficient detail and does contain all macros except for the print routine that follows the "Checking of Option buttons".


    The reason for the optionbutton macros is because they are continually selected through compilation, and I do not want to print the page until the user is prompted to check the selection for accuracy
    Once the selection is validated, the print routin should continue, which saves to PDF and emails out, hence I dont want the wrong format being sent out by accident


    The 3rd optionbutton is there because of page formatting "Size", as the page is setup to print only one of the columns. Also printing both columns is not required ever.


    I hope this is clear


    Thank you

    Hi Mumps
    Appreciate the response


    Sorry, but I may have confused things,


    The macro does not hide the columns, it only checks which option button is selected then repsonds with the message boxes.


    The columns are hidden by selecting the option buttons.


    As I mentioned, a print routine already exists, I just need to check which optionbutton is selected then run return the correct msgbox with options as per the original sample sheet requirements.


    If this cannot be acheived, I will have to rethink my options.


    Thanks

    Hi and thanks for the repsonse.


    I actully need this running from the command button Mumps,
    I have other macros that run on the onclick events of the option buttons


    The commend button is a print option. I already have the print code in place but need the optionbutton selections to be checked first


    I do need option 3 as the page size is formatted to print only 1 of the columns and also, should never print both columns.


    Thank you

    Hi All,


    I have 3 option butoons on a sheet which hide, resize and add columns to a worksheet


    What I would like to achieve is the following:


    -------------------
    If option button 1 is checked then pop up a yes/no msgbox to say "Option 1 selected, is this correct?",
    if yes then continue code
    if no end sub


    If option button 2 is checked then pop up a yes/no msgbox to say "Option 2 selected, is this correct?",
    if yes then continue code
    if no end sub


    If option button 3 is checked then pop up a vbokonly msgbox to say "You Cannot print this option",
    end sub
    --------------------



    Can anyone help with this please


    Thank you

    Hi Carim


    Thats great, thank you for the follow up.
    I modified the solution slightly as the same columns in the sample sheet are replicated accross the columns many times but with 1 column spacing between.
    so, the rows and columns calculations have moved, but nevertheless an awesome solution


    btw, that is some Array formula, and I have no idea what is means :duh:


    Amazing support once again.


    Thank you

    Hi Carim


    Your time and efforts are very much appreciated
    It is indeed the last version as everything works great.


    I just need to incorporate the extra 2 columns into the worksheet now.
    Should be just a case of moving stuff around, but array formulas can be awkward.


    Thank you

    for further clarity, and if its simpler, the calculations in column D do not need to be for each row.
    This can be a single calculation in D18 to show achievement of all the criteria.


    Thanks

    Hi Carim,


    In my haste to get a result, I omitted a vital criteria from this calculation.


    "y" must be present over the whole range Act 1, Act 2, Act 3 as a minimum but can include Act 4 and Act 5 if they are active.


    In the example I have inclued a sum column to show my thought process, but I cannot include this on the actual worksheet (no columns between the data sets)
    But if it is required, I could change a few things around.


    See attached which hopefully exaplains


    Thanks