Posts by viper

I have a spreadsheet that contains links to two other spreadsheets. It will only update the information if I have the two linked to spreadsheets open. It won't if they are not.

Re: disable update links in Single Worksheet saved with vba code

You can use this add-in from microsoft. Or try a search for removing formulas but keeping cell data.

http://support.microsoft.com/kb/188449
XL: Delete Links Wizard Available

HTH

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.

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:

Sub AddMenuItem()


Const MenuItemName = "Sepa&rate Cashiers"
Const MenuItemMacro = "CheckOpenWorkbook"
'this on error prevents the macro from not working if menu item is not present
On Error Resume Next
'if menu item already exists then delete first
Application.CommandBars(1).Controls("Tools").Controls(MenuItemName).Delete
    
'create new menu item under tools menu
    Set Item = CommandBars(1).Controls("Tools").Controls.Add
    Item.Caption = MenuItemName
    Item.OnAction = MenuItemMacro
    Item.BeginGroup = True
End Sub

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

Re: Add-in Menu Creation

Thanks Dave,

I was able to figure out how to do it. Wasn't the way in the link you sent but it works none the less.

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:

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:

Re: comparing two columns of text

Thanks, this works great, long code or not.

Thank you,

Re: comparing two columns of text

Wigi,
Is there a way to amend your code to include numberic entries as well?

Re: comparing two columns of text

Wigi your code was awesome. Worked super. Thank you.

Kris thanks for your suggestion.

Re: Clearing cell contents by color or formula

Nothing happens when I click the command button. I've got several cells that are colored Light Yellow that the user enters data in, I only want these cells cleared. But when I click the command button nothing happens.

I've attached a sample.

Thanks,

Hello,

I did a search and got this 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:

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.

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Dim rng As Range, c As Range
Set rng = Range("D15:AH22") 'set the range area
ActiveSheet.Unprotect ' unprotect the worksheet
For Each c In rng
    If c.Interior.ColorIndex = 6 Then 'checks if the interior color is yellow
        c.Locked = True 'if interior is yellow then lock the cell
    End If
Next c 'go to next cell in range
ActiveSheet.protect 'protect the worksheet
End Sub

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.

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