Posts by Insomniac

    I have created an addin which will add a control to the Window Command Button of the WorksheetMenuBar.It will list all visible sheets in Workbooks that are not hidden in the active Excel Application.


    Question: how to force the dropdown selection to always appear under the control?


    At present I set the width of the control to 380 and the .DropDownWidth = 364 for the list. This makes the dropdown appear under the control at a screen resolution of 1024x768. Is there a property I have overlooked?


    here is the addin:

    Hi there, may be a little late on this but i havnt been to this site for a while.
    I actually do as you describe for users with virtually no or just basic vba knowledge.


    I develop and test my macros then save the workbook and make it readonly, then copy it to the network server overwriting the old file.


    As long as it is kept as readonly no one can have edit writes to it and you can safely overwrite it with your latest version, which is then available to all your users whenever they open excel.


    The best way is to use a folder where only you have write access and everyonelse is read only.

    Hi Aaron, based on last tip i have ammended some code to this:


    Dim CBox As Control
    Dim I As Integer


    Private Sub UserForm_Activate()
    I = 0
    For Each CBox In Me.Controls
    I = I + 1
    If CBox.Name = "CheckBox" & I Then
    CBox.Caption = Cells(1, I).Text
    CBox.Tag = I
    CBox.ControlTipText = "Show/Hide " & Cells(1, I).Text
    End If
    Next CBox
    End Sub


    Where 26 checkboxes need to display row1 headings for Showing/Hiding catagories. No error check is used as i am specifically checking for checkbox names. Works great.


    Code to show/hide columns:



    Private Sub ShowCols()
    Set CBox = Me.ActiveControl
    ActiveCell.Activate
    If CBox.Value = True And Columns(Int(CBox.Tag)).Hidden = True Then
    Columns(Int(CBox.Tag)).Hidden = False: Cells(1, Int(CBox.Tag)).Select
    Else
    Columns(Int(CBox.Tag)).Hidden = True: Cells(1, Int(CBox.Tag)).Select
    End If
    Set CBox = Nothing
    End Sub


    Private Sub CheckBox1_Click()
    ShowCols
    End Sub


    Private Sub CheckBox2_Click()
    ShowCols
    End Sub


    Private Sub CheckBox3_Click()
    ShowCols
    End Sub
    'etc, etc 26 checkboxes


    Question: If I may, how to call 'ShowCols' without having 26 checkbox click calls??

    Hi all,


    I have some userforms where the ControlTipText does not display on some controls, is there some rule or guidelines to this. (I suspect I have them too close together or overlapping.)


    Also have multiple Frames on a multipage tab where only one frames controls ToolTipText works properly, is there a property i need to set?

    I think I misread your question, this will delete all rows from A23 down if any cell in the columns A-G are blank


    Sub del_rows2()
    'Delete rows from 23 down if column A-G cell is blank
    Dim rCount As Long, cCount As Integer
    Application.ScreenUpdating = False
    For cCount = 1 To 7
    For rCount = Cells(Rows.Count, 1).End(xlUp).Row To 23 Step -1
    If Cells(rCount, cCount) = Empty Then
    Rows(rCount).Delete
    End If
    Next
    Next
    Application.ScreenUpdating = True
    End Sub

    Hi Andy Pope,


    Yes what you say is correct but his conditional formatting formula references column A as absolute so when the cell H5 is copied to the other sheet it works.


    It dosnt seem to affect H5 in Add Employee sheet so I thought it would be a quick and easy solution.


    Else will have to alter the code to only copy and paste the value.


    It worked for me anyway.

    Hi again,


    the reason its not working is that your code is copying and pasting the cell from H5 in Add New Employee and pasting all including the formats.


    If you apply the conditional formatting from Employ Details column A to cell H5 in Add New Employee it works with the code that you have.

    "Hopefully it´s more then me that continue to read this thread since talking to myself is not my deal"


    Hi Dennis,


    no its not just you reading this thread, look at the view count.


    Some good info here for novice array users,like me...LOL

    Hi there,


    The reason its not working is the conditional formatting is not copied to the next row of Employee Details sheet.


    The easiest way would be to apply the conditional format to the whole of columnA


    Copy a cell with the formatting>>select Column A>>paste special .formats


    you would have to set cell A1 back to bold afterwards.


    or after last line in your copy code
    Sheets("Employee Details").Select

    'add this code
    Range("A1").End(xlDown).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=COUNTIF($A$1:A23,A23)>1"
    Selection.FormatConditions(1).Interior.ColorIndex = 3

    Thanx all for replies,


    However Excel Workbook security was not actually what I was asking about.


    I probably gave a bad example.


    Sometimes I want some code to run straight after a workbook has been saved for a variety of different reasons.


    So I have been looking for ways to simulate an AFTER SAVE event.


    Secuity is not neccessarily an issue and I do not want to use special Buttons, passwords etc. The users of these files are only data entry people with no other Excel knowledge, it needs to look as standard as possible.


    Another example:


    10 chidren each take turns opening a quiz workbook and entering answers on their own sheet.So we dont want them copying answers.They save as they go, each time their sheet is hidden before save and unhidden after save to continue.
    When the teacher opens the workbook all sheets are made visible to check answers.


    The teacher also knows very little about Excel and definately nothing about VBA.


    AJW - I checked out your sample workbooks and some interesting variations there on what I normally do. As all say there is no real security in Excel and I do appreciate that.

    Hi Richie,


    Ok I need to make sure that only certain users can see secure sheets, but other users can see general sheets.


    I have Open events to unhide sheets for a valid user.


    But, I cant inhibit a user from opening the workbook with Macros disabled so I have to ensure that whenever the file is saved the secure sheets are hidden.


    I dont want to force a save with the close event and hide sheets in case the user does not want to save his changes.


    So; I need to hide stuff - save - unhide stuff - user continues his work - all invisible to the user.


    Hope this makes it clearer.

    Hi Egad,


    yes, have to disable events because iam forcing a new save with ThisWorkbook.Save and cant have recursive calls to my code.


    the cancel = true negates the original save request, otherwise the workbook will be saved again with my sheets un hidden.


    It works for me, just wondering if anyone has a better idea?