General help
Posted by Matt Walker on July 17, 2001 6:38 AM
Hi, I started this conversation with Ivan earlier today.
It will proberbly make more sense if i start from the begining...
Once apon a time (only kidding!)
I've got a workbook containing 6 sheets. 5 of these sheets contain different type of stock and for ease i have a page contianing a summery of these sheets.
When the user starts excel(97) they are confronted with a userform rather than the workbook. This is designed so that they can edit the sheets in a speific way ie add stock and remove stock by selecting the relavant item form the drop down lists (these items are listed on the totals sheets using the following code)
Private Sub UserForm_Initialize()
Application.Worksheets("Totals").Activate
ComboBox1.ColumnCount = 5
ComboBox1.RowSource = "a5:a48"
ComboBox2.ColumnCount = 5
ComboBox2.RowSource = "a49:a159"
ComboBox3.ColumnCount = 5
ComboBox3.RowSource = "a188:a308"
ComboBox4.ColumnCount = 5
ComboBox4.RowSource = "a308:a320"
ComboBox5.ColumnCount = 5
ComboBox5.RowSource = "a160:a187"
What i'm having trouble with is the code that links these descriptions to their relavant worksheet and cell. i.ve done it this way because the worksheet contains the formula not the macro (which will allow people to edit the formula without messing around with the macro)
I want the user to select item stock x and remove 5 for example and upon closing that particular userform place 5 in the cell on sheet y which affects the total stock of item x.
i hope this isn't to confusing...
As for your questions; i'm using excel 97 and the code that i'm using (at the moment) for the txtboxes is this...
Private Sub TextBox1_Change()
Dim curpos As Double
curpos = TextBox1.SelStart
If Not ValidateNumeric(TextBox1.Text) Then
Beep
MsgBox "Please use numbers only!"
Else
Range("A1") = TextBox1.Value
End If
End Sub
Private Sub TextBox2_Change()
Dim curpos As Double
curpos = TextBox2.SelStart
If Not ValidateNumeric(TextBox2.Text) Then
Beep
MsgBox "Please use numbers only!"
Else
Range("A1") = TextBox1.Value
End If
End Sub
Private Sub TextBox3_Change()
Dim curpos As Double
curpos = TextBox3.SelStart
If Not ValidateNumeric(TextBox3.Text) Then
Beep
MsgBox "Please use numbers only!"
Else
Range("A1") = TextBox1.Value
End If
End Sub
Private Sub TextBox4_Change()
Dim curpos As Double
curpos = TextBox4.SelStart
If Not ValidateNumeric(TextBox4.Text) Then
Beep
MsgBox "Please use numbers only!"
Else
Range("A1") = TextBox1.Value
End If
End Sub
Private Sub TextBox5_Change()
Dim curpos As Double
curpos = TextBox5.SelStart
If Not ValidateNumeric(TextBox5.Text) Then
Beep
MsgBox "Please use numbers only!"
Else
Range("A1") = TextBox1.Value
End If
End Sub
Private Function ValidateNumeric(strText As String) As Boolean
ValidateNumeric = CBool(strText = "" _
Or strText = "-" _
Or strText = "-." _
Or strText = "." _
Or IsNumeric(strText))
End Function
followed by...
Private Sub Userform_close()
Range("sheet1!plantadded") = TextBox1.Value
End Sub
I'm a complete begginer at writing these macros and have compiled this through the (greatly appreciated) comments that you have posted in reply to my pleas.
I hope this casts some light on what i'm trying to achieve.
Thanks again,
M Walker
Posted by Joe Was on July 17, 2001 4:06 PM
Try a Case or Load Var.
You may try to redirect your code based upon a choice the user makes or a condition. Below are two ways to do this.
This is a search utility that uses a Case statement to direct options. Below is the If-Load Var. method.
Sub Search_Complex_Code()
Dim Temp As String
Dim Tsearch As String
Dim Numsub
Dim Mcan
Temp = Application.InputBox(Prompt:="Please enter the Complexity Code to search for;" & Chr(13) & "Use a [ 7 digit search code ], only!" & Chr(13) & "You may use [ ? ] as a place holders, for digits which are not important!" & Chr(13) & "Example: ?G?????, [6 codes, 7 digits, ODA = 2 digits!]", Title:="Enter the Complexity Code to Lookup!", Type:=2)
If Temp = "False" Then
Numsub = 2
ElseIf Temp = "" Then
Numsub = 3
Else
Numsub = 1
End If
Select Case Numsub
Case 1
Tsearch = "" = " & Temp & """
With Worksheets("Load")
.AutoFilterMode = False
.Rows(5).AutoFilter
.Rows(5).AutoFilter Field:=6, Criteria1:=Temp, VisibleDropDown:=False
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Search").Cells(1, 1)
Application.CutCopyMode = False
.AutoFilterMode = False
End With
Sheets("Search").Select
Range("A1").Select
Case 2
Sheets("Search").Select
Range("A1:A6").Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("Load").Select
Range("A1").Select
Case 3
Mcan = MsgBox("Search Canceled, search criteria is Blank?", vbOKOnly, "BLANK SEARCH!")
Sheets("Search").Select
Range("A1:A6").Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("Load").Select
Range("A1").Select
End Select
End Sub
This is the If-Load Var. Version of a color change macro.
Sub ColorGet()
'
Dim vTest
Range("A1:F1").Select
'If value tests.
'Gray.
If Range("A1").Value = "" Or Range("B1").Value = "" Or _
Range("C1").Value = "" Or Range("D1").Value = "" Or _
Range("E1").Value = "" Or Range("F1").Value = "" Then
vTest = 3
End If
'Red.
If ((Range("A1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("B1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("C1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("D1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("E1").Value < 40 And Range("A1").Value <> 0)) Or _
((Range("F1").Value < 40 And Range("A1").Value <> 0)) Then
vTest = 1
End If
'Blue.
If Range("A1").Value >= 40 Or Range("B1").Value >= 40 Or _
Range("C1").Value >= 40 Or Range("D1").Value >= 40 Or _
Range("E1").Value >= 40 Or Range("F1").Value >= 40 Then
vTest = 2
End If
'Colors.
'Red.
If vTest = 1 Then
Selection.FormatConditions.Delete
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
'Blue.
If vTest = 2 Then
Selection.FormatConditions.Delete
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
'Gray.
If vTest = 3 Then
With Selection.Interior
Selection.FormatConditions.Delete
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End If
End If
Range("A1").Select
End Sub
Hope this helps. JSW
