Posts by vwankerl

    Re: how can i organize my macro which got told having a length too long by excel

    First, why are you using ";" instead of "," to separate parameters in the various parts of the formula? For example, you use "SUBSTITUTE($F4;"/";"µ";1)", instead of "SUBSTITUTE($F4,"/","µ",1)". It seems you are doing that through out the formula. Is there something I don't know about separating parameters in formulas with a semicolon?

    Second, to help with a formula like this it is very helpful to see the workbook you are trying to use it in. Can you post a sample workbook that we might be able to see what you are actually trying to do? Very possibly there is a simple formula that will do what you want with out all the repetition of functions. But to know what that is we need to see some data and workbook structure you are working with.

    Re: Delete Buttons on Worksheet_Deactivate and Create Buttons on Worksheet_Activate

    Here is some code I use to do something very much like you are asking about:

    gwksDashboardSht is a global variable pointing to one of the sheets in my workbook. It could be Activesheet or any other worksheet object.

    Note that these buttons are the Forms buttons not Activex buttons.

    This code works in Excel 2003 and Excel 2007. I haven't tested it with Excel 2010 but have no reason to believe it won't work there also.

    I call the RemoveButtons routine from Workbook_Open and Workbook_BeforeClose procedures. I call MakeButtons routine from the Workbook_Open event procedure. I call the RemoveButtons from both because I have found there are times when the workbook got saved but the BeforeClose event didn't fire. And buttons seem to accumulate if you are careful about adding and removing.

    If you have any questions please ask. The unasked question never gets answered

    Re: Hide certain columns if Column is greater than 17


    here is some code that I think will work for you:

    Let me know how it works.

    Re: Import and Flatten Delimited Record-Type Text File

    I downloaded your two files and am looking them over to see if I understand what you are wanting to do. So far a process is forming (an algorithm as we say in classical programming). I need some more information. For the 5 options on record type 16, is there only 5 unique values for the Prepayment premium columns? By that I mean, does "Yield Maintenance - CMT" always go to "Option 1" and no other value goes there? Likewise, "Declining Premium" to "Option 2" and no other value? From the data I glean option values of "Yield Maintenance - CMT", "Declining Premium", "Prepayment Lockout", "Yield Maintenance", and "Other". Are there any other possible values for this list?

    This looks like a fairly straight forward program. I will work on a solution as I have opportunity this weekend.

    Re: Hide certain columns if Column is greater than 17

    How does the value in CK get there? Is there a formula that does a sum or something else? As you are entering data into the other columns, if you are summing values into CK, do you want to rehide columns as soon as the value in CK > 16? That is, suppose in column E you put a value of 8 which will unhide column G. Then you put 9 in column BA which will unhide column BC. If you are doing a simple sum in CK, you will have 17 in the CK column now. Should all the columns be rehidden as soon as you put the 9 in BA? If not, what should be the trigger to cause the columns to be rehidden?

    Re: Set different activecell intersection to do different coding

    You are running into a problem because you are changing the Activesheet and expecting the Activecell to continue to point to the Target sheet. Activecell always references the Activesheet!

    Also, Range("D2") or Range("D3") continue to reference the Sheet the Target references (I am assuming that is Sheet1). You must remember that Range("D2") is shorthand for ThisWorkbook.Sheet1.Range("D2"). You must be sure you are aware of the "fully qualified" cell references when using a shorthand reference.

    I would suggest you re-think what you are trying to do here. Maybe there is a better way to do it.

    Re: expand vba length or shorten sub

    There is most certainly a way to write this routine so it is very short. We just need some more information. You say you need to go to column CJ and row 241, does that mean if there is something in E4 or E5 or E241 then you need to unhide column G? And likewise for all other columns and rows? If that is the case then you are doing the same code for almost 21000 cells!!
    Here is some code for you to try. It should produce the same results as your code, but more efficiently.

    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Cells.Count <> 1 Then Exit Sub
       If Target.Value > 0 Then
          lCol = Target.Column
          ActiveSheet.Columns(lCol + 2).EntireColumn.Hidden = False
       End If
    End Sub

    Let me know how it works (or not as the case may be.)

    Re: cell comment, font color


    Try this routine:

    Note that Split creates an array of strings from a delimited string. The Characters method requires a start position and length value much like the Left string function.

    Re: cell comment, font color

    Here is some code I put together that does what you want:

    I don't know for sure what the error you got was caused by, but I suspect the use of Split in the Characters property might have something to do with it. I moved the calculation of the start position and length out of the Characters property reference.

    Re: cell comment, font color

    Try this:

    rngTgt.Comment.Shape.TextFrame.Characters(CInt(Split(itm, ":")(0)), CInt(Split(itm, ":")(1))).Font.Color = RGB(128,4,20)

    Be sure to change the RGB numbers to reflect the color you really want. I have no idea what color those three numbers will produce.

    I am guessing that the literal constant that you were using is not the proper type for the Color property. By using the RGB function
    you will get the correct type.

    Re: Event Macro to add logic to the cells in the worksheet

    I just uploaded a workbook based on the one you posted earlier. I have added code to it to do what you want in as generalized a way as I could.

    There is code in the ThisWorkbook module and in three regular modules.

    In ThisWorkbook:

    We capture two events, the Workbook_Open and the Workbook_SheetChange events. Workbook_Open simply initializes the global variable rngBlueColumns before any processing takes place so when a user tries to change a non-blue column it will act as though it were locked.

    Workbook_SheetChange captures any user changes to a cell. if the cell is NOT in a blue column, the change is Undone and goes back to the user. It the cell is in a blue column, the routine ChangeAll is called passing the range of the changed cell.

    Note that Application.EnableEvents is turned off prior to making any cell changes and then back on before returning to the user.

    In MGlobals module:

    Option Explicit
    Public rngBlueColumns As Range

    We merely define the global (Public) Range variable so we can initialize it in the Workbook_Open code and use it in the Workbook_SheetChange code.

    In MFindRange module:

    This code is from
    It may be old but it still runs like a champ!

    Finally, in Module1 module:

    Here we just save some information in local variables, call the FindRange routine to search for all the entries with the same value as the Target row. If we find any (should always find at least 1) we loop through the cells found and change the corresponding column to the new value.

    I just realized, you wanted to prompt the user with a MsgBox if they wanted to make the change to each found cell. Here is the code change to do that:

    If Not rngFound Is Nothing Then
          For Each rngTemp In rngFound
             If rngTemp.Row <> inTarget.Row Then
                If MsgBox("Make this change to row " & rngTemp.Row, vbYesNo) = vbYes Then
                   Cells(rngTemp.Row, lTargetCol).Value = strChangeValue
                End If
             End If
          Next rngTemp
       End If

    Replace the red code in the previous segment with this code to use the MsgBox prompt to approve the changes.

    I hope this works for you. If you have any questions please ask. My coding isn't complete until you understand how it works and why.

    Remember -- The unasked question never gets answered!

    Re: Unhide multiple sheets based on username

    First of all, since you have a correspondence between UserIDs and Sheet names, let me suggest that you have a VeryHidden sheet that allows you to put that correspondence into some kind of table. For instance, each column has a UserID as the first line and under that is a list of all the Sheets that should be unhidden for that UserID.

    With that Sheet code can be written to manage sheets very easily.

    I will try to set up a sample workbook that will show these ideas.

    Re: Is it possible to have a formula's cell reference multiply?

    I figured out a formula to do it:

    =IF(INDIRECT(ADDRESS(StartRow+RowIncrement*(ROW()-1),4,1,,"Sheet1"))=0, "N/A",INDIRECT(ADDRESS(StartRow+RowIncrement*(ROW()-1),4,1,,"Sheet1")))

    You should have two Defined Names : StartRow and RowIncrement

    For StartRow define it as "=47" (without the quote marks) (47 per your example - change it to what ever is the first row on sheet1)
    For RowIncrement define it as "=33" (without the quote marks) (33 per your example - change it to what ever is the number of rows to jump)

    Please note that the formula works because you are putting it in A1:A100. If that is not the case, the factor "Row()-1" will need to change base on the first row you are putting the formula into.

    Let me know how it works.

    Re: Event Macro to add logic to the cells in the worksheet

    I downloaded your sample workbook and will look at it later. You say the "Yellow" columns don't change but the "Blue" ones can. Do you mean that the "Yellow" ones should be treated as though they were locked (Protected)? The behavior you describe above for column F, should that be the way the other "Blue" columns should behave also?

    Re: Is it possible to have a formula's cell reference multiply?

    There is probably a way to do this in a formula (maybe using Offset or some kind of Indirect function), but I would probably do a quick-and-dirty one-off macro to populate the cells properly. It would be a simple loop over the cells A1-A100 that incremented the reference by the required amount for each cell and put the formula into the the cell. Once done, you can either remove the macro or leave it there in case it was needed in the future.

    Re: Update object handling from Excel 2003 to 2010

    I am unable to test this, but I would guess from looking at your code that the ws.Paste either changes or clears the Selection, or that the type of the Selection does is no longer Shapes? Also, in the general case, Selection (as well as some other objects like Range), unless qualified will reference the Activesheet. It appears that the Activesheet changes right before the Paste. That would imply that since Selection is not qualified it would be trying to point to something on the new Activesheet which may not be correct. Check these things.