Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
How can I make this work for 2 comboboxes an the same userform and make it
that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
Private Sub CbxMfg_Change()
Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
Mr. Phillips,
How could I catch the error in the following code and allow the user to continue on to enter the information. Right now my database is empty and I am designing this as I go? I keep getting an error at the ** mark due to the fact that the columns are empty. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double bEnablEvents = False sSelected = Me.CbxMfg.Value dCount = 0 If Not dCount = 0 Then FrmManu.Show FrmProduct.Hide End If For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then ** Me.CbxProd.AddItem Cells(dX, 2).Value dCount = dCount + 1 End If Next End Sub "Bob Phillips" wrote: Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
Don't do it that way, I showed you a better way.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, How could I catch the error in the following code and allow the user to continue on to enter the information. Right now my database is empty and I am designing this as I go? I keep getting an error at the ** mark due to the fact that the columns are empty. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double bEnablEvents = False sSelected = Me.CbxMfg.Value dCount = 0 If Not dCount = 0 Then FrmManu.Show FrmProduct.Hide End If For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then ** Me.CbxProd.AddItem Cells(dX, 2).Value dCount = dCount + 1 End If Next End Sub "Bob Phillips" wrote: Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
I've been trying it your way and I keep getting an error, I've tried to
manipulate the line of code all different ways and it just is not working "Bob Phillips" wrote: Don't do it that way, I showed you a better way. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, How could I catch the error in the following code and allow the user to continue on to enter the information. Right now my database is empty and I am designing this as I go? I keep getting an error at the ** mark due to the fact that the columns are empty. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double bEnablEvents = False sSelected = Me.CbxMfg.Value dCount = 0 If Not dCount = 0 Then FrmManu.Show FrmProduct.Hide End If For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then ** Me.CbxProd.AddItem Cells(dX, 2).Value dCount = dCount + 1 End If Next End Sub "Bob Phillips" wrote: Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
Mr. Phillips,
I am having a heck of a time getting this thing to work properly, I fix one error and another happens. I know we like to keep thing in the forum but I am not good at explaining what it is that I'm trying to do and without posting a maddive post with all my code, I would like to know if I could e-mail you my excel file for you to take a lokk at, if not thanks anyway and I will just keep plugging away at it one error at a time? "Bob Phillips" wrote: Don't do it that way, I showed you a better way. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, How could I catch the error in the following code and allow the user to continue on to enter the information. Right now my database is empty and I am designing this as I go? I keep getting an error at the ** mark due to the fact that the columns are empty. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double bEnablEvents = False sSelected = Me.CbxMfg.Value dCount = 0 If Not dCount = 0 Then FrmManu.Show FrmProduct.Hide End If For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then ** Me.CbxProd.AddItem Cells(dX, 2).Value dCount = dCount + 1 End If Next End Sub "Bob Phillips" wrote: Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
I only have tomorrow available, but you could send it to me
bob dot phillips at tiscali dot co dot uk -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, I am having a heck of a time getting this thing to work properly, I fix one error and another happens. I know we like to keep thing in the forum but I am not good at explaining what it is that I'm trying to do and without posting a maddive post with all my code, I would like to know if I could e-mail you my excel file for you to take a lokk at, if not thanks anyway and I will just keep plugging away at it one error at a time? "Bob Phillips" wrote: Don't do it that way, I showed you a better way. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, How could I catch the error in the following code and allow the user to continue on to enter the information. Right now my database is empty and I am designing this as I go? I keep getting an error at the ** mark due to the fact that the columns are empty. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double bEnablEvents = False sSelected = Me.CbxMfg.Value dCount = 0 If Not dCount = 0 Then FrmManu.Show FrmProduct.Hide End If For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then ** Me.CbxProd.AddItem Cells(dX, 2).Value dCount = dCount + 1 End If Next End Sub "Bob Phillips" wrote: Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this work??
Mr. phillips,
I will send it to you tomorrow morning, I have it at work. "Bob Phillips" wrote: I only have tomorrow available, but you could send it to me bob dot phillips at tiscali dot co dot uk -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, I am having a heck of a time getting this thing to work properly, I fix one error and another happens. I know we like to keep thing in the forum but I am not good at explaining what it is that I'm trying to do and without posting a maddive post with all my code, I would like to know if I could e-mail you my excel file for you to take a lokk at, if not thanks anyway and I will just keep plugging away at it one error at a time? "Bob Phillips" wrote: Don't do it that way, I showed you a better way. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... Mr. Phillips, How could I catch the error in the following code and allow the user to continue on to enter the information. Right now my database is empty and I am designing this as I go? I keep getting an error at the ** mark due to the fact that the columns are empty. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double bEnablEvents = False sSelected = Me.CbxMfg.Value dCount = 0 If Not dCount = 0 Then FrmManu.Show FrmProduct.Hide End If For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then ** Me.CbxProd.AddItem Cells(dX, 2).Value dCount = dCount + 1 End If Next End Sub "Bob Phillips" wrote: Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value If IsError(Application.Match(sSelected, Worksheets("ProCode").Columns(1),0)) Then Me.Hide FrmManu.Show End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mekinnik" wrote in message ... How can I make this work for 2 comboboxes an the same userform and make it that is the value from combox('CbxMan').value doesn't match anything in column 'A' or a new name is typed in it will open a new userform? Here is my current code. Private Sub CbxMfg_Change() Dim sSelected As String Dim dX As Double, dCount As Double sSelected = Me.CbxMfg.Value dCount = 0 For dX = 2 To Worksheets("ProCode").UsedRange.Rows.Count If Cells(dX, 1).Value = sSelected Then Me.CbxProd.AddItem Cells(d, 2).Value dCount = dCount + 1 End If Next If Not dCount = 0 Then FrmManu.Show Unload Me End If End Sub AND Private Sub UserForm_Initialize() Dim coll As Collection Dim i As Long Dim itm 'fill coll with values from column A Set coll = New Collection On Error Resume Next For i = 2 To Worksheets("ProCode").UsedRange.Rows.Count coll.Add Range("A" & i).Value, Range("A" & i).Value Next i On Error GoTo 0 For Each itm In coll Me.CbxMfg.AddItem itm Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make this work? | Excel Discussion (Misc queries) | |||
Looking for a way to make this work. | Excel Worksheet Functions | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
can't make it work, I need help | Excel Worksheet Functions | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions |