Posts by viper

    Re: Add-in Menu Creation


    I see your concern and although I didn't mention it I do have this line of code in my add-ins workbook close event to remove the menu item upon Excel closing.


    Code
    On Error Resume Next
    
    
    Application.CommandBars(1).Controls("Tools").Controls(MenuItemName).Delete

    Re: Add-in Menu Creation


    Sure Dave, sorry for my inconsiderateness. To create my add-in I put my macro into a new workbook and saved it as an *.xla file.


    I then wrote a second macro to put a menu item under the tools menu using this code:



    As noted above I set up constants so as not to keep calling the macro or menu time name over and over.


    After all my macros were written and assuring of proper execution I then put a simple one line code in my workbook open event: AddMenuItem


    Then under tools, I clicked add-ins, browse, found my xla workbook and selected OK. Now every time excel opens my add-in is available.


    Thanks

    Hello all,


    I am wanting to make a macro into an add-in. An add-in is basically a workbook with macros that is opened when excel is correct? I know how to create an additional menu item under an existing one, but I'm wanting to create my own menu with this add-in item under it. I've used this code to try and create the new menu and placed it in the workbook open:


    Code
    HelpIndex = CommandBars(1).Controls("Help").Index
    
    
    Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=HelpIndex, _ 
    Temporary:=True)
    
    
    NewMenu.Caption = "&Separate Cashiers"


    But ran into an error on the first line of code. I saved the workbook with the add-in macro in it as an .xla and used the addins under tools to have it open each time. This was too unsuccessful.


    Where my error?

    Re: vlookup or match/index


    Thanks to both. I thought there would be some way to use offset but didn't go your route. Never could figure out the index/match formulas but seems to be more understandable now.

    Hello all,


    this one has me confused. I know I've done it before but poor at record keeping, I have a column with dollars (F6:F19), in column A I have names, in cell I6 I use MAX to find to the find biggest number. In cell J6 I need to put the name associated with that number.


    Name 1.00 3.00 need name associated
    Name 2.00
    Name 3.00


    If I use INDEX I can get the row number associated with the number in I6, using VLOOKUP I can get the number.


    Thanks,

    Hello,


    I'm wanting to have message box display a message before the macro does it's thing. Is there a way to add a checkbox to a message box that tells the user that if they do not want the message displayed again to check the box?


    Also, if the user does check the box and doesn't want the message displayed again, how would I go about changing the code to a comment?


    Seems kind of easy if I use a userform of inputbox but changing that line code to a comment I'm just not figuring out.


    Thank for any help:

    Hello,


    I did a search and got this code:


    Code
    Dim c As Range
    For Each c In Activesheet.UsedRange
         If c.Interior.ColorIndex = 27 Then 'this is supposed to be "Light Yellow"
            c.ClearContents
         End If
    Next c


    I also tried to use this one:


    Code
    Dim c As Range
    For Each c In Activesheet.UsedRange
         If c.HasFormula <> True Then
            c.ClearContents
         End If
    Next c


    Neither have worked. My color pallatte uses two yellows, yellow and Light Yellow. Is there any code that can tell me what the index numbers are of my current color pallatte?


    Thanks,

    Re: comparing two columns of text


    Sorry, it didn't look that bad when I wrote it. What I'm wanting, is to see if on a spreadsheet I can enter any phrase in a cell, say "Thank you". Then in another cell enter a second phrase, say "Your Welcome". The worksheet then would count each letter in the first phrase so actually it would show 1 of each letter for the first phrase. Then it would count each letter in the second phrase. Then it would subtract the letters for the second phrase from the first phrase and tell me how many letters I need. Kind of doesn't make sense but attached is a worksheet that hopefully describes it. I have a second worksheet that uses "Countif" to count the letters and I may have to use this one but I have to enter each letter of each phrase in separate cell.

    Hello all, I wondering if there is a way to compare text in two columns and then give me a number of each letter. I'll explain below, I have to change a readerboard with new messages every soften. I would like to be able to type what it says in one column and then what I need it to say in another column and then add each number of each letter in the first column and add each letter of the same letter in the other column and then tell me how many of that letter I need or have extra.


    Example:


    Current message New message Needed letters


    Hello Thank T = 3 - 2 = +1 over


    this you H = 2 - 2 = 0 needed


    is I A = 1 - 2 = 1 needed


    a have N = 0 - 1 = 1 needed


    test it K = 0 - 1 = 1 needed


    any ideas will be appreciated.


    Thank you,

    Re: Attendance sheet protection.


    It works for me, if you are specifing a password like this:
    ActiveSheet.UnProtect Password:="yourpasswordhere"
    then you will have to have the same password for each sheet. If you are not using the same password for each sheet then in your code you will need to have it unprotect the sheet based on the password for each sheet.


    But leaving the same password for each sheet works and I don't get the password pop-up.

    Re: Attendance sheet protection.


    I'm not sure why but it wouldn't work for me either, in my color palette the color index number 36 you had was a light grey, I changed the cell color to yellow (Index 6) and this code worked fine for me. I put it as Andy suggested so that it will work on any sheet you choose, although to protect each sheet with a different password you will need to add code based on the active sheet but to protect them without a password this works for all sheets.


    HTH

    Re: Getting a macro to select text around the active cell


    Use the offset function, if you are the wanting the cell behind the activecell then put this: Activecell.Offset(0, -1).Copy where the 0 is the row reference which means that it will choose a cell in the same row of the activecell. The -1 is the column reference which with it being a negative number it will choose the cell one column behind the activecell column. Using that, if the activecell is B2 then it will select A2.


    syntax of Offset is: Offset(Row, Column), you can choose any cell on the worksheet by specifing a number for row and column. If you are wanting to select a cell in a Row above the activecell then use a negative number for Row, Positive number for a cell below activecell, same for the column number.


    HTH

    Re: Attendance sheet protection.


    Put this in the worksheet event you want, change the range value and you should be set. If the worksheet is protected then it will need to be unprotected before this works so you will need to add activesheet.unprotect password:="your password" at the beginning. And then protect it afterwards.


    Code
    Dim rng As Range, c As Range
        Set rng = Range("B4:J24")
        For Each c In rng
            If c.Interior.ColorIndex = 6 Then
                c.Locked = True
            End If
        Next c


    HTH