Posts by TheGlovner

This line here will need altered to reference your actual combo box:

With Me.ComboBox1

Afraid I can't, can't download the files in the work PC, firewalls will just eat it right up.

Specifically what wasn't working? Error? Can you debug and step through and confirm what line the error occurs on?

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:

Option Explicit


Private Sub PopulateComboBox()


  Dim varColItem  As Variant
  Dim colListData  As Collection


    With Me.ComboBox1
      Set colListData = DeDuplicate(Range("AccountsList"))
      For Each varColItem In colListData
        .AddItem varColItem.Value2
      Next varColItem
    End With


    'Clean up
    Set varColItem = Nothing
    Set colListData = Nothing
   
End Sub


'Pass in the single dimension range and this will only add the unique items into a collection to be passed back to the calling code
Public Function DeDuplicate(ByRef rngInputRange As Range) As Collection


  Dim colTempCollection As Collection
  Dim varWrkCell        As Variant
 
    Set colTempCollection = New Collection
    For Each varWrkCell In rngInputRange
      If IsInCollection(colTempCollection, Trim(CStr(varWrkCell.Value2))) = False Then
        colTempCollection.Add varWrkCell, Trim(CStr(varWrkCell.Value2))
      End If
    Next varWrkCell
   
    Set DeDuplicate = colTempCollection
   
    'Clean up
    Set colTempCollection = Nothing
    Set varWrkCell = Nothing
   
End Function


'Evaluates whether the item is currently in the collection or not
Private Function IsInCollection(ByRef colTempCollection As Collection, ByRef strItemKey As String)


Dim varTempItem As Variant


On Error GoTo ErrHandler


'If the object can't be set then it isn't currently in the collection (i.e. it's unique)
Set varTempItem = colTempCollection.Item(strItemKey)
IsInCollection = True
GoTo ExitHandler


ErrHandler:
IsInCollection = False


ExitHandler:
'Clean up
Set varTempItem = Nothing


End Function

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: Vlookup using wildcard for fetching the data

Can you not just set the false to true in order to get approximate matches?

Although this wouldn't be fool proof and may give false results based on how your reference data is sorted.

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.

Re: Find specific sheets in a workbook and combine them all in a sheet in same workbo

Single post and no return.

Screams "cross post already answered" to me.

Re: How to count number of unique combinations inside a table of data?

Are you always evaluating the sample against the first 3 digits? Or if that combination of three digits appeared anywhere in the string would it still count?

i.e. if evaluating 123 and the number was 181237 then would it still count due to 123 being digits 3, 4, & 5?