Papadopoulos
04-09-2010, 03:42 PM
This is a small snippet of the mess I have created.
I am hoping that you can help me improve how this is done (This is actually one of the shorter examples of the nightmare that I have created and now need to clean up)
In this case, I am using a drop down to reference a list from another sheet. Based on the selection I fill in a couple of cells of information.
So far I have done this through a series of if/then statments (16 for this one alone!) which means that anytime the list changes...
Not pretty I know.
Sub SheetSize()
'sets width, height, and number up based on choice - 16 option s
If Worksheets("Sheet1").Range("szIND").Value = 1 Then
Worksheets("ref").Range("sizeDesc").Value = Worksheets("sizeRef").Range("A2")
Worksheets("Sheet1").Range("Width").Value = Worksheets("sizeRef").Range("B2")
Worksheets("Sheet1").Range("Height").Value = Worksheets("sizeRef").Range("C2")
Worksheets("Sheet1").Range("qUP").Value = Worksheets("sizeRef").Range("D2")
Worksheets("ref").Range("cutsPer").Value = Worksheets("sizeRef").Range("E2")
' Remove macro from bleed check box and enter null val for the bleedVal cell
' that is used in the quote and hide the check box
ActiveSheet.Shapes("Check Box 47").OnAction = ""
ActiveSheet.Shapes("Check Box 47").Visible = False
Worksheets("Sheet1").Range("bleedVal").Value = ""
' End checkbox manipulation
' Perform the same for the imposition button
ActiveSheet.Shapes("imposeCalc").OnAction = ""
ActiveSheet.Shapes("imposeCalc").Visible = False
' End interface manipulations
Range("forms").Select
ElseIf Worksheets("Sheet1").Range("szIND").Value = 2 Then
Worksheets("ref").Range("sizeDesc").Value = Worksheets("sizeRef").Range("A3")
Thanks,
David
I am attaching the file
I am hoping that you can help me improve how this is done (This is actually one of the shorter examples of the nightmare that I have created and now need to clean up)
In this case, I am using a drop down to reference a list from another sheet. Based on the selection I fill in a couple of cells of information.
So far I have done this through a series of if/then statments (16 for this one alone!) which means that anytime the list changes...
Not pretty I know.
Sub SheetSize()
'sets width, height, and number up based on choice - 16 option s
If Worksheets("Sheet1").Range("szIND").Value = 1 Then
Worksheets("ref").Range("sizeDesc").Value = Worksheets("sizeRef").Range("A2")
Worksheets("Sheet1").Range("Width").Value = Worksheets("sizeRef").Range("B2")
Worksheets("Sheet1").Range("Height").Value = Worksheets("sizeRef").Range("C2")
Worksheets("Sheet1").Range("qUP").Value = Worksheets("sizeRef").Range("D2")
Worksheets("ref").Range("cutsPer").Value = Worksheets("sizeRef").Range("E2")
' Remove macro from bleed check box and enter null val for the bleedVal cell
' that is used in the quote and hide the check box
ActiveSheet.Shapes("Check Box 47").OnAction = ""
ActiveSheet.Shapes("Check Box 47").Visible = False
Worksheets("Sheet1").Range("bleedVal").Value = ""
' End checkbox manipulation
' Perform the same for the imposition button
ActiveSheet.Shapes("imposeCalc").OnAction = ""
ActiveSheet.Shapes("imposeCalc").Visible = False
' End interface manipulations
Range("forms").Select
ElseIf Worksheets("Sheet1").Range("szIND").Value = 2 Then
Worksheets("ref").Range("sizeDesc").Value = Worksheets("sizeRef").Range("A3")
Thanks,
David
I am attaching the file