I need specific text for specific drop down selection unless it is one of these 3 selections...

[JeremyA1976]

I am going to try to be as descriptive as possible and I am not even sure if this is possible... Thanks in advance for your patience.

I have a tab for sheet metal and plate that is part of material charge out for our shop. The person entering the information can choose from various item descriptions in a drop down. They can then choose the gauge from a drop down. Then they enter the square footage and it calculates pounds and total price. They will then hit a submit button and it sends the calculations to main charge our form on sheet 1. Below you will find the item description choices (not all there, but gives you the idea) they will have to choose from.

16ga GALVANIZED "COIL"
18ga GALVANIZED "COIL"
20ga GALVANIZED "COIL"
ETC
ETC
10ga GALVANIZED "SHEET"
12ga GALVANIZED "SHEET"
14ga GALVANIZED "SHEET"
ETC
ETC
BLACK IRON
STAINLESS STEEL
ALUMINUM

The 3 inputs they need to make is: (1)Item Description (2)Gauge (3)Square Footage

The problem is with the gauge. Some of the item descriptions already contain the gauge because they need to be tracked different with a different account code. For example, we track our coils so we know when to order another coil. These coils are 5' wide by varying lengths (depending on gauge), so if our shop foreman enters a charge out for 1000 sq.ft of 20ga. Galvanized "Coil", It still has an input for the gauge, which could be entered in wrong. This does not affect any formulas, but it will not be correct and can get confusing if not caught.

Here is my question: Is there a way to grey out or lock the gauge input cell unless the item description reads "Black Iron", "Stainless Steel" or "Aluminum? Or can it read the gauge in the description and mirror it in the gauge input, but also allow the user to choose from a drop down of available gauges with those three items mentioned above? These 3 items are the only 3 of 24 that would require a gauge input.

Or, maybe a code when i hit the submit button that would read the description and only report the gauge input if it recognizes one of the 3 items?

 

[JeremyA1976]

The item description cell is located at B2
gauge input cell is at B5
The cells to compare item description are located at:
Black Iron = A50
Stainless Steel = A51
Aluminum = A52

At the button click, I want the code to compare B2 to the range A50:A52 and if it matches either of the 3, I would like the gauge to report on my main "CHARGE OUR FORM" (Sheet1). If it does NOT match any of the 3 cells, then I don't want it to report a gauge to my main form.

 

[JeremyA1976]

*Here is the code for button click on METAL SHEET & PLATE



Sub ShMtlSend()


Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim DestRow As Long
Dim x As Long


Set ws1 = Sheets("METAL SHEET & PLATE")
Set ws2 = Sheets("CHARGE OUT FORM")


Application.ScreenUpdating = False


With ws2
DestRow = .Cells(.Rows.Count, "C").End(xlUp).Offset(2).Row
'QTY
.Range("N" & DestRow).Value = ws1.Range("F46").Value
'ACT
.Range("M" & DestRow).Value = ws1.Range("C13").Value
'COST
.Range("K" & DestRow).Value = ws1.Range("F45").Value
'LBS
.Range("I" & DestRow).Value = ws1.Range("B11").Value
'SQ/FT
.Range("G" & DestRow).Value = ws1.Range("B8").Value
'MATERIAL
.Range("C" & DestRow).Value = ws1.Range("B2").Value
'Gauge/Thickness
.Range("A" & DestRow).Value = ws1.Range("B5").Value
'PRICE/UNIT
.Range("O" & DestRow).Value = ws1.Range("F43").Value

End With


Application.ScreenUpdating = True


Set ws1 = Nothing
Set ws2 = Nothing

Sheets("CHARGE OUT FORM").Select

End Sub