Posts by jo15765

    I am wanting to use VBA to filter a worksheet. I am reading the userID's from the worksheet like this

    Dim userID As Variant
    Dim uID As Variant
    userID = Sheets("Data").Range("B2:B101").Value

    Then I try to do the below, to use VBA to filter it filters out all rows acting as if the value does not exist?

    For Each uID In userID
        Sheets("Data").Range("$A$1:$BV$153").AutoFilter Field:=2, Criteria1:=userID

    Re: Add Total Columns Through VBA

    Derk that is exactly what I was needing. Is their a way to update the syntax so that it does borders like so:

                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .Weight = xlMedium

    jindon helped me out greatly over on this thread
    but now I need to add a total to each sub sheet that is created, and add a total present column which counts the number of Xs from the Master sheet. Attached is a sample workbook showing my desired output on worksheet 1G. Attached is my current vba syntax (again provided by jindon not my own code) how would this be tweaked to add the 2 total columns like needed?

    Re: VBA To Move Master Data To Secondary Sheets

    Quote from jindon;756260


    AHHHH - I see what my problem is. I added a few dates to "ALL" worksheet, and it seems that if their is no "X" for the initials below the date it throws the erorr I received. See attachment (and this is a snippet, their will be multiple dates on the "ALL" worksheet that will not have any "X" added yet)

    Re: VBA To Move Master Data To Secondary Sheets

    Hello, and thank you for the prompt response! For me, it will create the 1st worksheet properly, then on this line I get a type mismatch error

    Private Sub SendToWS(dic As Object)
    .Cells(2, 2+1).Resie(dic(e).items()(i).Count).Value=Application.Transpose(dic(e).items()(i).items)

    So only the 1st "Sub" sheet is created.

    I have a sheet called ALL and in column B each individual has been assigned a room number. For each unique room # I need to create a individual worksheet for. Then Take each individual name and deposit that into A2 going down of the corresponding worksheet. Then starting in A2 take each date found on the master sheet and SUM each of the X for the individual for that day. I have attached a sample book showing what I need, if someone could assist I will be indebted :)

    Re: Sum Checked Checkboxes

    Quote from mikerickson;756093

    If the checkboxes are from the Forms menu, you could use code like

    The nested IF statements cannot be combined into an AND because if the shape isn't msoFormControl, trying to read its .FormControlType will error.

    Ah yes, that achieves the same goal as well. I should have done a little bit better explaining myself (or provided sample workbook). I have column A rows 2 - 80 with names in it and each name has checkboxes in columns B - whatever and I need a count for each row of what is checked / not checked.

    Re: Sum Checked Checkboxes

    Quote from shknbk2;756090

    I'm not sure of your data, but a quick test of adding checkboxes and running this macro to set the linkedcell works. If you don't want the linked cell to be directly underneath the checkbox, you'll have to modify the code to offset the row_val and col_val values. Use the COUNTIF worksheet function to count the true values.

    This code finds the row in which the top of the checkbox is in as well as the left side of the checkbox for its column.

    Ah yes, this is exactly what I was after. I didn't realize it was that labor intensive/time consuming to achieve this task, but it gets the job done. I am fine with the linked cell being under the checkbox. Is their a way to set the text to white so that you do not see True under a check, but I can still SumCountif from it?

    I just inherited a spreadsheet that has roughly 1200 checkboxes on the sheet. I have googled and discovered that in order to get a count of the checks (or at least it appears) you must have the Cell link set. I have spot-checked around 100 of these and none have that value set.

    1) is their a macro that will assign this value?
    2) is their a way to count checked boxes w/o this being assigned?

    What the spreadsheet looks like is C3:ZZ86 have checkboxes that are either checked or unchecked. And I need to have my sum of checked unchecked in A3 through etc.

    I would also like for this to be done completely VBA free, well VBA can be done for the initial set-up, but moving forward if a check box is checked I want 1 to be added to the corresponding total.

    Can someone please assist with this?

    Re: Copy Image From SourceWB To All WB's In A Directory

    And just in case anyone ever needs this down the road, this is the full syntax I ended up going with.

    Re: Copy Image From SourceWB To All WB's In A Directory

    This is the syntax I ended up going with, not sure how exactly it differs from my above post, but this one is working! Thanks John_w

    Re: Copy Image From SourceWB To All WB's In A Directory

    Alright, so I discovered that the Picture name from workbook to workbook is always one of two picture names, which helped simplify things. With my syntax here I am trying to assign the image (new logo) to the same dimensions of the old logo but the new image (new logo) is not imported with the same height/width specs of the old logo? Why is that?

    Re: Copy Image From SourceWB To All WB's In A Directory

    Quote from John_w;751351

    My first procedure showed how to do that. Get a reference to the image (specify workbook and sheet as necessary) and use its Width and Height properties.

    PS - there, not their!

    I tried this syntax, which is the cell directly above the Picture I want to get dimensions of, but it is not selecting nor deleting it? And the dimensions given are not for the image, I want to see...