Posts by Halvar

    Re: Data pull based on Reviewer Name


    Sounds like a good candidate for a pivot table report, no? Go to Insert => Pivot Table, and after building the table add a filter field which will be set to "Failed."

    Re: Macro to Highlight a Range


    Bimmy, you can still do this with conditional formatting then, will be longer but since you're not comfortable using VBA updating someone else's code would be a pain anyway:


    1. Create a "mapping" tab listing all the accounts you'd like to highlight;
    2. Use CountIf to create a helper column on your data tabs: if ticket in your current row is on the mapping tab, coutif will return 1;
    3. Use conditional formatting with a formula looking to the helper column: if helper column shows 1 (in other words, if the ticket exists on the mapping tab), paint the ticket cell in red.


    I think in your case this will be easier than using VBA. FYI, by selecting multiple tabs you can edit all of them at once. So, even if you have 30 tabs, for as long as they are set up in the same fashion you should be able to make this update fairly quickly.

    Re: Lock certain cells in excel


    1. Unlock ALL cells on the sheet by selecting the entire sheet, right-clicking and selecting Format Cells; then on the Protection tab uncheck "Locked" box;
    2. Select only the cells you'd like to protect, go to Format Cells again and check the box;
    3. Protect the sheet with a password. Only the cells selected in step 2 will be protected now.

    Re: Allow Input on blank cells ONLY, when sharing file


    The only immediate and relatively simple recommendation for you would be to use Track Changes functionality of Excel to keep track of all the users that made updates to the workbook. This will at the very least give you visibility into who's doing what.

    Re: Macro to Highlight a Range


    Bimmy, a couple notes:


    1. The code I've posted operates on the assumption the 3-digit code is unique across all alphanumeric combinations, if that's that's not not the case - alphanumeric data is a completely different story. Still very doable, but will take some time to code.
    2. Macros can be triggered by Workshee_change events. Meaning, whenever data is pasted macro will be run and highlight everything in red.
    3. Would be more than happy to look at your code, but as cytop has mentioned, given your additional requirements this work shouldn't be posted in the help forum. If you're willing to pay for this work I can help you out though.

    Re: Macro to Highlight a Range


    this will be a good start:


    Re: Allow Input on blank cells ONLY, when sharing file


    Not aware of the option that will allow you to use different passwords for different cells on one sheet. The idea behind unlocking cells is that those cells remain open for editing permanently, unless this is changed with someone who has a password.


    What you can do is run a macro every time a cell value is changed to automatically protect that cell, but that will cause problems when people enter incorrect data and want to correct themselves. A few other options to explore - split inputs into separate tabs that will feed into one calculation tab (so, Sheet1 for Employee1, etc.), or using extensive data validation on the sheet to ensure the data entered makes sense.

    Re: Allow Input on blank cells ONLY, when sharing file


    Yep, quite easily actually. Are you familiar with the worksheet protection feature (Review=>Protect sheet)? If so, select the cells that should be left unlocked, right-click on them and go to Format Cells, select Protection tab, and uncheck the "Locked" box. Now, when you protect the sheet with a password, all the cells except for the "unlocked" ones will be protected from user input. You can also use VBA to loop through all the empty cells and lock/unlock them.

    Re: Macro to Highlight a Range


    hey Bimmy, why not use a combination of RIGHT formula and conditional formatting?


    Say, column C contains your ticket range. In column D1 put =right(C1,3) to pull ticket number. Then, using Conditional Formatting on column C select New Rule => Use a formula to determine which cells to format => enter "=and(D1>388,D1<404)"

    Hello all,


    I'm protecting my sheet using


    Code
    ws.Protect Password:="password", UserInterFaceOnly:=True


    Theoretically, this should allow me to make any changes to the sheet through VBA without the need to unprotect the sheet. However, the code bugs out on the following line:


    Code
    With ws
        .Columns("Z:Z").Insert Shift:=xlToRight
    End With


    I already know that you need to run .Protect with UserInterFaceOnly every time the workbook is open, it's adding protection using my Workbook_Open event. But, it seems like there are exceptions to what can be manipulated on a sheet with VBA even if UserInterFaceOnly is set to True, in my case adding new columns. Is anyone able to confirm/deny this? I would prefer not to unprotect the sheet every time I run the macro, but I was curious if that's even an option.

    Re: One more IF - dont know where to put it


    Hey Kuba - just saw your post today.. My recommendation going forward would be to use SUMIFS if possible. It's easy to use, syntax is not that complex (http://www.techonthenet.com/excel/formulas/sumifs.php). You probably won't be able to replace all of your formulas with it (countif formulas might have to stay the way they are), but try to keep it simple.


    I prefer when people explain what they're trying to do with words instead of figuring out what their formula is supposed to do, so if you struggle with any other formula on your sheet let me know what youre trying to achieve and I'll help you figure it out.

    Re: If weekday/weekend give cell value


    Hi, try the following for weekdays (adjust rows as necessary):


    Code
    =IF(AND(N2<>"Saturday",N2<>"Sunday"),"Y","N")


    and this for weekends:


    Code
    =IF(OR(N2="Saturday",N2="Sunday"),"Y","N")

    Re: Listview error handler if onclick is null or empty


    and, sorry, I missed an Exit Sub statement in that code..