Posts by TheGlovner

    Have you considered saving it as an .xlsb? This would remove the XML functionality but if you're not communicating with online applications then it's probably fine to drop it. This usually roughly halves the size of the sheet.

    Another thing to consider (I work in assurance and we see this a lot with applications people have designed in the past) is whether the tool is saving the data to the actual application itself.

    What happens in these instances is that as the tool grows older the amount of data in it grows and it makes it unwieldy (i.e. taking ages to open).

    I've mitigated this with some of our applications in house by using the application for processing but outputting the results to a separate .csv or .xlsb.

    If you insert the images as ActiveX objects (found in the developer>controls>insert menu rather than Insert>Picture) then it will allow you to easily assign code to various actions like a single left mouse click.

    You can then attach the appropriate VBA code to unhide the appropriate sheet and move the focus to the place on that sheet you want the hyperlink to take you to. So it would work just like a hyperlink but would ensure that the sheet is unhidden first.

    Then for the various sheets just have them all contain a Worksheet_Deactivate event which re-hides the sheet on leaving it.

    Code attached to the ActiveX Picture object would be something like this:

    Private Sub AccessWorksheet()
        ThisWorkbook.Worksheets("WorksheetNameToAccessHere").Visible = xlSheetVisible
    End Sub

    Although I'd be inclined to move the functionality out to another subroutine in a code module and just pass the name of the worksheet to that from each ActiveX control rather than coding the unhide functionality each time.

    Then something like the following held in each hidden worksheet in order to re-hide on leaving it;

    Private Sub Worksheet_Deactivate()
        Me.Visible = xlSheetVeryHidden
    End Sub

    Hopefully that gets you started.

    Would you not be better to collect the data prior to populating the combo box then parse this to create a new list which can then be passed into the combo box list?

    Something like the following:

    Re: Accessing Values from Names Manager

    I use 2010 in the office and that would produce the same issue you are having.

    I'd always use Range as opposed to Name (unless I'm actually manipulating the named range on the sheet in some way).

    Re: Excel VBA Macro

    In addition to what's already been said.

    I think the Error1004 is being caused by the fact that your Range Property is prefixed with a period, this would imply that you've already confirmed the reference string using a With xxxx statement.

    But no sign of the with statement in the code.

    So you either need to encapsulate that line within a With Statement, put the earlier part of the reference (sheet) prior to the period or remove the period all together.

    Re: Absolute references aren't absolute when pasting into Table

    Can't download the sheet due to being at work.

    What is creating the reference? Is that happening within some VBA code? If so post the bit of code that creates the string and then passes it to the cell. That's no doubt where your issue is.

    My suspicion would be that the code tracks the row it's on as a variable and then creates a string for the reference something like:

    strRefString = "O$" & lngRowNumber & ":O" & lngRowNumber & ",K$" & lngRowNumber & ":K" & & lngRowNumber

    When it should be something like:

    strRefString = "O$" & lngStartRow & ":O" & lngRowNumber & ",K$" & lngStartRow & ":K" & & lngRowNumber

    Re: Reference Worksheet Codename using a string variable?

    Woah! This must have been one of my first posts here, lol.

    Can't even remember why I was doing this let alone why we used the Index Property.

    I think I was developing an add-in though so it had something to do with not knowing the real codenames in the sheets that were referencing the add-in.

    I could be talking utter bollocks though, so don't quote me.

    Re: Macro Help

    No problem glad to help.

    As a general rule, if you only need to cut up the string in order to construct a new string and the location of the characters is consistent, then you can usually cobble something together quite easily using the substring commands (Left, Mid, Right) in a formula.

    If you need to start interpreting, altering the string in order to produce something that doesn't exist currently then start to veer towards the code angle.

    Code is more powerful, but a lot of people use it to over engineer their solutions which adds complexity and overheads where it's not needed.

    But if you have the time then by all means go for it, you need to try it to learn it.

    Just noticed it may be missing a period before the last part of the string "PSD" should be ".PSD". I've changed it up in the original post now.

    Re: VBA to Copy Visible Cells and Delete Blank Cells and Zero Values

    So essentially you have filtered the data down to display a subset.

    You want code to extract the values that are viewable whilst ignoring rows that have been hidden (filtered) and rows that contain nothing ("", vbnullstring).

    Is that correct?

    If so should it just start on row 16 or is that because that's the first visible row? (Not a good idea to hardcode something like that if it could change following different formatting or resorting of the data)

    Also, what should prompt it to end the extract of the data before outputting the results in AQ?

    Re: Combining two conditional statements into one formula

    No problem glad to help.

    Nested If statements basically work of the idea that they continue to evaluate the conditions until one is found to be True (or potentially false depending on where you are nexting them in the formula), then it stops at that point.

    So you just need to ensure that the order of precedence for your results are incorporated into the order you nest the Ifs in. Hopefully that makes sense.

    Re: Macro Help

    I should have confirmed, hopefully obvious though, that InputCellRef will need replaced with the cell you have input the reference data to.

    Re: Macro Help

    Yes it makes more sense now.

    try this:

    ="AC_VOLUME_4:Assets:HR Images:" & left(InputCellRef, 1) & ":" & left(InputCellRef, 2) & ":" & left(InputCellRef, 3) & ":" & InputCellRef & ".PSD"

    Re: Combining two conditional statements into one formula

    Decide which of the two options take precedent then nest the if statements.

    So for example if the output of 1 is the more important then:

    =IF(AND(I2>=$C3,J2<=$D3),1, IF(AND(I2>=$E3,J2<=$F3),2,""))

    So if the first clause is satisfied, you get a 1.
    If the second clause is satisfied, you get a 2.
    If neither clause is satisfied, you get a blank (or a space as you had it but not sure if you had a good reason for that, I've removed the space in the example).

    Re: Macro Help

    Does it need to be a macro?

    Could you not just use:

    ="AC_VOLUME_4:Assets:HR Images:" & "ReferenceWhereverTheNextBitComesFrom" & "752388_V1" & ".PSD"

    As a formula? Or is there a reason for using code?

    Re: Macro to help match and sort data

    Unfortunately I do most of my help waiting for things to run while I'm in the office and the firewall will stop me downloading your file.

    Essentially you need to take the code in the first box, create a module in your VBA project and paste it into that.

    Then create a Class Module within your VBA project and paste the contents of the second box into there.

    Then on the sheet where the user makes the selection I've created a named range called "SelectionInput".

    The Sample data that the code needs to check through is a named range called "SampleList".

    After that it's just the sheet references.

    If that still doesn't make sense, perhaps someone else could download the file and paste the code in and post it back for you.