Solved: Recipe Creation Wizard help

[shydaddy]

Hi, I know virtually nothing about VBA :dunno(except for how to spell it) but, found myself volunteering to try and create a recipe creation document. Somehow, I have managed to get half done with the document and need some help or guidance.
So, the person will click on the ?Select any Tool? button and will choose from a list of tools, which is listed in cells A43 ? A55, and then they will enter the process that they will be creating. Ok, depending on which tool they select, VBA will populate a list of tanks and depending on which tank is selected, they will select the chemical(s) available in these tanks. I have set this up so it will cycle up to 6 steps for each of the tool. (Actually, I left out three of the tools because I wanted to know if there was a cleaner way to write up the code). After they press the next button the data is displayed between B6 through about E14 or so (depending on how many steps are needed)

:banghead:The problem that I am having is the actually processes/chemicals are listed in rows 40-48 with colors, red, green and black. I need the data listed in E7 ? E14 column to match the colors from below depending on what tool and tank they choose. Is there anyone that will be willing to help me to either clean up the code and or give me guidance on how to fix this problem? I thank you in advance for any help that you can provide.

[shydaddy]

Did I ask for the impossible? Anyone have any ideas please let me know since I am beating my head against the wall trying to figure this out:banghead:

[Bob Phillips]

I suspect that like me, everyone was having trouble in understanding what you need to do.

Walk us through an example.

[Aussiebear]

I spent a couple of minutes working throught this issue this morning and I ame unstuck. Now admiditily I not the smartest brick in the pallet, but I struggled.

I copied all the data rows to a second sheet and then tried to follow the concept of colors ( ugly American version of colours), I ran into a brick wall.

So then I tried to follow the concept of "Stepping".... Got so close i coould smell the roses, but no cigar.

Could you please, as XLD has suggested, post an example of how you step through this project.
:dunno

[Charlize]

1. Maybe you better use a worksheet / tool
2. When you select a tool you use just one worksheet with the options for that particular tool
3.
Column A : When A selected, you can choose from A, B or C
Colmun B1 : A, B, C
Column C1 : When A, choose from A or B
Column C2 : When B, choose from D
Column D1 : A, B
Column D2 : D
Column E1 : You choosed for A
Column E2 : You choosed for B
Column E3 : You choosed for D

Just an idea, result of letting the mind go with the flow.

Charlize

ps. Dependable dropdownboxes ?

[shydaddy]

Thanks for responding and sorry for the late response from me, I am working 18 hour days right now. Ok, I will try and explain everything. First, all the raw data is located between A40 and CJ 51.
The person that is creating the recipe will select the Select A tool button which displays the Select a Tool Userform. Depending on what tool they select, will determine which group of userforms will display for the the recipe creator to add Step1 through Step 6. Meaning if they select WDNS4D5200 in the Select a Tool userform, then WDNS4D5200 Step 1 through Step 6 will be displayed. This part is working perfectly so far. There are Three things that I am needing help with.

First is the color problem. in Column E rows 7 - 12 under Process, If any of the processes that are selected contains a coma (,) I need those processses to turn Red and if any contain an apostrophe ('), I need those to be blue as in the example of the newly attached file. I can get by with this not working if it can't be done by substituting not qualled for the (,) and not set up for the (').

Second and the big thing that I need help with is having the golden curve calculations populate in Column H.
These will be simple calculations that are based on what is listed in that same row under column C and Column G. For example, if the tank selected under C7 is BOE/QEII the calculation would be something like " =LEFT(G7,3)/1.6 & " Seconds"". I know that there is a different/more reliable way to extract the numbers beside "left(G7,3) but I can't remember it right now. Likewise, if ONB/T2 was entered into C8 H8 would populate with something like " =LEFT(G8,3)/2.5 & " Seconds"" as seen in the attached document.

The third thing I would like is to have the Send Email button not be visable until show up until Done is pressen on any of the other userforms. This I can get by with it as it is.

Thanks for you help. I do have a question for Charlize, what do you mean by the p.s. dependable dropdown boxes?

[Aussiebear]

I think Charlize was suggesting that you might think about a series of linked dropdown boxes. This way if you were to choose a particular tank, it then offers only certain Step 1 selections, which in turn then only offer particular Step 2 selections and so on.

[shydaddy]

I don't know how to do the linked drop downboxes or couldn't get it to work and found part of the code that I used in an example, figured out how to make it do what I wanted and thats how I got here.

[rbrhodes]

Hey Idaho, BC calling...

I spent some (way too many) hours on this and believe I've a working example of what you were trying to get. BTW: For someone that doesn't know VBA you did great!

It still needs a lot of work (error handling in particular) but here 'tis.

This should keep you busy just reading the comments in the code. Note: there is code in the Workbook module, Worksheet module, Forms and Modules...

[shydaddy]

RBRHODES,


WOW, I am in awe at what you did. Thank you so much for all the help. I knew that there had to be an easier way to get the end results but just didn?t know how to do it. Any suggestions for me as to where I can learn how to produce the kind of code that you did?

You mentioned that it needs a lot of work but, I can't see anything as of yet and as far as error handling?It seems to be working great and the code (the little that I have looked at so far) seems great and I can even understand a little bit. I tired to put it through the motions and only found one little thing that I changed because I just found out about it last night at work. Anyway, the below (with a little bit of changes) is what I came up with that was sort of working for the change colors portion of issue # 2 that I was working on.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
If Union(Target, Range("E7:E15")).Address = _
Range("E7:E15").Address Then
Application.EnableEvents = False
For Each rCell In Target
If Len(rCell.Text) >= ??? Then ?not sure how to set this up so if a (?) turn red and if a (,), turn green
rCell.Font.Name = "Arial"
rCell.Font.ColorIndex = 5 ? different # will be used for correct color
?' Elseif ?This not working ?cuz, I don?t know what to use
?' Len(rCell.Text) >= ??? Then ?not sure how to set this up so if a (?) turn red and if a (,), turn green
?' rCell.Font.Name = "Arial"
?' rCell.Font.ColorIndex = 3 ? different # will be used for correct color
Else
rCell.Font.Name = "Arial"
End If
Next
Application.EnableEvents = True
End If


I know it?s not all set correctly but it was my attempt at getting this working.

So how much do I owe you for the help. If I wasn?t poor, I would hire you to help me with some of the other projects that I am stumbling through.
Once again, thank you for helping me with this.
Shydaddy

[Charlize]

Probably not what you wanted, but here's an example of what I meant by dependable dropdownboxes.

Charlize

[rbrhodes]

Hi again,

This bit of code in the UpDater sub does the red/blue thing using 'InStr' (Look in help for how to use InStr...)



'//Check for chars: Comma and Apostrophe. Colour as desired
If InStr(listTwo, ",") <> 0 Then
'Found comma
Range("E" & StepNum).Font.Color = vbRed
ElseIf InStr(listTwo, "'") <> 0 Then
'Found apostophe
Range("E" & StepNum).Font.Color = vbBlue
End If


However I neglected to reset the font colours! This is what I meant about 'needs work...

For now though, to solve this bug just add this line to the 'Private Sub Button1_Click()' routine:

Range("E7:E20").Font.ColorIndex = 0


See example below.


Private Sub Button1_Click()

'//No event code till done
Application.EnableEvents = False
'//No select/Selection pairs
Range("B4:H20").ClearContents
' Range("B4:H20").Select
' Selection.ClearContents



'//ADD THIS LINE///

Range("E7:E20").Font.ColorIndex = 0
'///END



Range("A1").Select

'//Show command loads and shows Form
' Load SelectTool1

'//Hide email button
ActiveSheet.Shapes("btnSendEmail").Visible = False

SelectTool1.Show
'//Reset
Application.EnableEvents = True
End Sub


Your example of coding (for learning purposes) woulf look like this:


Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, Range("E7:E15")).Address = Range("E7:E15").Address Then
'//Could also use this:
' If Not Intersect(Target, Range("E7:E15")) is nothing then
Application.EnableEvents = False

'Use with so Target is only 'looked up' once
With Target
'Use Instr to check for desired character '
If InStr(.Text, "'") <> 0 Then
'Change colour.
.Font.ColorIndex = 5
'Note: VB has colours as well ie vbRed, vbGreen, vbBlue, etc
'Example: .Font.ColorIndex = vbRed
'Set font
.Font.Name = "Arial"
'Use Instr to check for desired character ,
ElseIf InStr(.Text, ",") <> 0 Then
'Change colour
.Font.ColorIndex = 3
'Set font
.Font.Name = "Arial"
End If
End With

Application.EnableEvents = True

End If
End Sub



BUT! Since I already have a 'Worksheet_Change' event that would not be a good idea! (You can't have 2...) Mine handles the Golden rule stuff (I hope)