Posts by richadj4

Re: Code not working on some computer

is it on the .name = s line? I suspect that you are trying to name the worksheet something invalid here (such as a name that already exists).

Re: Dynamic Userform Design

Inserting VBA code on the fly is........... complex and RARELY a good idea. I've done it, usually with fairly complex addins and/or dictator applications, but.... its messy.
1) It's not debuggable. like at all.
2) It's REALLY difficult to work out what your code is doing when. Aborting the vba at different points I've ended up with 6 userform objects in vba, and no way of telling what code is still running.
3) Can you explain why your above code snippet needs to be added to the objform codemodule at run time rather than design time?

Re: Dynamic Userform Design

Okay, a couple of thoughts - It's been a while since I've done the theory, so bear with me (and mistakes are possible). You possibly already know some or all of this

The userform2 and CtlExitCls are both "classes". When they are "instantiated" (usually by set x = new userform2 type command), an object is created from that class. That object has it's own code, methods events etc. When you create a userform2 OBJECT, this also creates ctlExitCls objects BELONGING to that object. If/when the form is unloaded, the object is destroyed, as all all objects belonging to it, and all code, methods, etc of those objects. no code on the userform object (or any subobjects) will run after the form is unloaded. unloading must be the VERY last thing you do in code relating to the object in any way. I don't believe (based on the examples I've seen), that this is what you are doing. I think you have code that belongs to a subobject of the form that unloads the form mid execution, thus destroying itself while it still has lines to execute. This can cause anything from vba exceptions to excel crashing.

Re: Dynamic Userform Design

where are you actually unloading the form? Still in the same place?

Re: Dynamic Userform Design

hmmmmmm, I REALLY don't like some of what you're doing, and it's hard to tell if thats just "not the way I'd do it" or actually wrong.

Part of your problem could be the unload userform2 command in the cmbClass module. I THINK, the instance of the is object is part of the userform2 object, so when you unload userform2 you are attempting to unload something that is currently executing.

When I try it, if I move the msgbox unloading to ABOVE the unload command, I SEE the msg before excel dies, I don't see the message after. So the unload is dying, and I SUSPECT it's sying because you are loading something running.

Re: Map text to numeric rankings

Is that a sample, or a complete definitive listing?
This formula "=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"u",""),"(P)",""),"e",""),IFERROR(SEARCH(" ",B4)-1,999))" Translates a raw in b4 to a clean.

Re: Dynamic Userform Design

Example won't run, missing a stack of sheets and data headers.

I've looked at the code and I'm struggling to understand. I don't get why you need to "close down "Userform2", delete the form in vba, then run the Macro to add back in "Userform2" again."

If you instead have a sub that builds a single line, you can call this x time when first run activate the form, and then call it ONE MORE TIME when you need to add.

If you put each line on it's own frame, you would have the ability to delete a frame, move all "lower" frames up by x pixels to fill the gap, move them down x pixels to create a gap to put a new frame in etc etc etc etc

Re: Dynamic Userform Design

Hmmmmmm, Somewhat behind the 8 ball here, but I have developed a fairly complex userform system that SOUNDS vaguely similar to what you are trying to do. Mine is currently read only (it's for display not editing), but some of the concepts may be transferable.

I have a userform called ufRecords, which is pretty much a big blank form and some code, and contains a collection of clbentry objects
I have a classModule clbentry. This class module contains a frame and a "collection" of labels and faux-labels with events.

The userform has a method to add 1 (or more) "records". It does so by creating a new clbentry object, passing it the position of the LAST clbentry object and the information to go in it. The clbentry object then creates a new frame in the appropriate location, creates a bunch of label objects and puts the new information in them. Critically with objects at the bottom of the "tree" (i.e labels on frames in a clbentry object in the form) experience an event, ALL they do is invoke a method on the parent object and pass it information about the event, and an id, that can be used to identify the object. All events are therefore "dealt" with by the userform. For example - when a label has mouseover, the label registers an event and tells the clbentry object that the object "lHorse", registered a mousemove event, passes it the button,shift,x and y parameters of the event, and the "tag" of the label. clbentry then passes all that information AND a clbentry "tag" to the form. The form then performs the appropriate action (in my case displays text relating to the original label in a giant floating label -like a tool tip)

Not sure if this makes ANY sense or is likely to be helpful. But *I've* found it to be a helpful way to have a form respond intelligently to events on sub objects created at run time (my form FREQUENTLY has 1000+ clbEntry object, each with ~40 labels, so coding for each of those would be a freaking nightmare).

Re: VBA Run-time error 438

Well, Haven't tested this is any way, but at a glance your final line "mychart.export" - you haven't set mychart to anything anywhere..............

Re: VLOOKUP return multiple values

Interesting.
I'm not sure WHICH part of that is not valid in 2013. Array formulas definitely work. It MAY be the iferror function, I'm not 100% sure when this was introduced. Try it without the ifferror. it will still work, but you will get #N/A returns AFTER the valid information (so if there are 2 instances to be returned, it WILL return those, but lines 3 through 6 will be #N/A, There are other more complex ways to get around this, IF this is in fact the issue.)

Re: VLOOKUP return multiple values

Also, potentially stupid remark, make sure everytime you are entering or editing the formula, you are doing it with ctr shift enter. A quick way to check, is when you have entered it, if you see the formula in the formula bar, it SHOULD be surrounded in {} to indicate it is an array formula.

Re: Copy Certain Columns to Multiple Sheets

Well, tbh, probably the quickest and easiest way to do this is just formula's. Put a named range around the data on your first sheet (called Main), ideally this should by dynamic. If you don't know how to to that, just make it "bigger".
Then on all of the sheets, put this formula in cell a2 "=INDEX(main,ROW(),MATCH(A$1,INDEX(main,1,0),0))" then fill across and down. Set your column headings, and this will automatically pull information from the main table based on column headings.

Re: Search table return multiple value's

1) yes the 5000 can be altered to a bigger number. It can safely be bigger than the table.
2) don't futz with the "2"!
3) This formula will return the Horse if it is in column B and the time if it is column C (the column()-1 indicates where it returns things from)
4) Is it currently working at all? I'd make sure it is working correctly re the sample you uploaded. This is obviously very small data, but when you can make it work there, you should be able to make it work elsewhere.

5) Dynamic named ranges are AWESOME. There is a very good article on them somewhere on this site. In brief a named range (you may already know) describes a range of cells, so instead of saying $F$2:$F$5000 you can say ThisStuff. Dynamic named ranged goes further by making ThisStuff change size depending on what's in it. You set up a dynamic named ranged in the name manager, and the formula in this case would be "=OFFSET($F$1,1,0,COUNTA($F:$F)-1,1)"

I wouldn't recommend trying this until you are making it work correctly with a fixed range.

Re: Vlookups, Vba, Multiple results

on what line? and can you upload a sample set?

Re: Choose text from drop down list will populate lists of texts automaticaly in next

Not a problem
No unfortunately
There should be a link at the bottom of the post where you can "give reputation" for a helpful response.

Re: Choose text from drop down list will populate lists of texts automaticaly in next

"However I do NOT need any colouring in A column" - The is no colouring in the code. If the colouring in column A (The purple and yellow) should not be there, then it should be manually removed by changing the fill of the cells in excel, nothing to do with VBA

On a completely unrelated note, if you want the table to fill down to more than row 23, then change the 23 in the code to a higher number

If Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 Then

OR (for unlimited rows) delete that line entirely. ( you will also NEED to delete the FIRST endif below it).

Re: VBA to Change cell colors based on several factors/worksheet

Sorry, but the sample is not making a lot of sense. "clients name on worksheet 1 which are C5:CE5. Clients name are also listed in worksheet 2 A12:A55." - Both of these areas are completely blank.

Not sure what you mean by "matching", do you want "1"s to automatically appear on sheet2 when (something I don't understand) is done on sheet1? this should be pretty simple.
Metallic levels - A6:A340 -well, we have info in A6-A29, so this is a start. Not sure how this relates to b11:i11 though. There are a variety of "metallics" that are not present in the second range.

"If a client elected a certain metallic the cell should stay white" wut? what cell, where, why.

"Attached is sample along w/a tab on how it should look when completed" I don't understand why there are multiple white empty cells. I don't understand how this relates to sheet2 (if it relates to sheet2). I don't understand why there are "Transferred to client"s in column C and G, but not D, E or F, I don't understand why there are no "Updated Versions" or " No enrolments"

In short, I don't understand what you are trying to accomplish

Re: VBA Scripting Dictionary Vlookup help please

I've looked at this about 5 times, procrastinating in the hopes someone else will come solve but, but I guess I'll give it a go.

1) Okay, you start by looping through sh and adding each entry to a dictionary. As both an item and a key (why?????) FYI this

dict.Item(NewIDBroker(i, 1)) = NewIDBroker(i, 1)

is REALLY bad practice.

2) you then loop through each line in sh2 and check to see if it's in the dictionary. If it IS, then you overwrite it with "not new" (not sure why, but okay), if it isn't then you...... overwrite it with itself (whhhhhhy)

3) Finally you then loop through the dictionary and output everything to sh2 again. You haven't at any point removed anything from the dictionary because it was already in there.

I...... really hope I'm missing something obvious.

Re: Subtract IF multiple conditions

I Thought I understood, but the example actually makes things more confusing

In your initial instructions you stated: Sold Sheet: Coulmn E is location, H is SKU, and I is Quantity.
In your example sold sheet: column E is blank, Column H is blank, and column I is blank AND outside the table. Nothing in the sold table appears to relate to the order table with the possible exception of "ship to Address 1"

In the results sheet, the first line had 13, now has -299, a difference of 312. The total for the "texas" lines in the sold sheet = 332. At that point I gave up.

Re: Choose text from drop down list will populate lists of texts automaticaly in next

uhhhhhh, coloring was already there? nothing to do with the code, just change the fill to nofill

If you WANT repeated names, just delete this:

If Target.Parent.Columns(1).Find(what:=c.Value) Is Nothing Then

(leave the rest of that line)