Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with code issues
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default Help with code issues
Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
..AddItem R(1, 2)
End If
Next R
..SetFocus
If .ListCount 0 Then
..ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
..ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Help with code issues
Probably irrelevant to your problem, but I was curious about the difference
in the range references in these two lines. Since you are essentially doing
the same thing from different approaches, it seemed odd that one range has
seven cells more in one test than it does in the second test.

V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
---
For Each R In Worksheets("test").Range("A1:A17")
---

"Mekinnik" wrote:

Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default Help with code issues
I was just testing it to see if it would work if one sheet had more data in it.

"JLGWhiz" wrote:

Probably irrelevant to your problem, but I was curious about the difference
in the range references in these two lines. Since you are essentially doing
the same thing from different approaches, it seemed odd that one range has
seven cells more in one test than it does in the second test.

V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
---
For Each R In Worksheets("test").Range("A1:A17")
---

"Mekinnik" wrote:

Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Help with code issues
Disregard, I should have read the entire post before commenting.

"Mekinnik" wrote:

Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Help with code issues
You said this worked in testing. Did you have the initialize code using two
worksheet references and non-contiguous range references for RowSource when
you were testing? I have never seen that done before. Exactly where do you
get the error message when you try to run the code?

"Mekinnik" wrote:

Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default Help with code issues
I get an unspecified error code @ the .clear, in addition its really not
giving me an error it just that it keeps showing in cbxprod the same names as
in cbxman when it is suppose to show the names that match. this is what each
sheet looks like:

Sheet(MANCODE)
manufacturer name address ect, ect.
name 123 name
mane ******
so on and so fourth


Sheet(ProCode)
manufacturer name Product name
name green
mane blue
same red
name red
mane green
name blue

Its suppose to show for each say "name"(cbxman) in column 'A' show in
cbxprod.list (green,red,blue).
Which like I said it does in the test version, however in the production
version it keeps just showing whatever is in the cbxman list.

"JLGWhiz" wrote:

You said this worked in testing. Did you have the initialize code using two
worksheet references and non-contiguous range references for RowSource when
you were testing? I have never seen that done before. Exactly where do you
get the error message when you try to run the code?

"Mekinnik" wrote:

Can someone please help me with an issue I am having with some code. When I
use the code for testing it works just fine, however when I apply it to the
whole macro code it does not work properly, any suggestions would help.
Thank you all

Here is the code in the test form

Private Sub Cbo1_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.Cbo1.Text
V = Application.Match(S, Worksheets("sheet1").Range("A1:A10"), 0)
If IsError(V) = True Then
frm1.Hide
frm2.Show
End If
If IsError(V) = False Then
With Me.Cbo2
For Each R In Worksheets("test").Range("A1:A17")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If
End Sub

Private Sub UserForm_Initialize()
Cbo1.List = Sheets("sheet1").Range("A1:A6").Value
If Cbo1.Value = "" Then
frm1.Hide
frm2.Show
End If
End Sub


Here is the code when it is applied to my macro(it does not work)

Private Sub CbxMfg_Change()
Dim S As String
Dim V As Variant
Dim R As Range
S = Me.CbxMfg.Text
V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0)
If IsError(V) = True Then
FrmProduct.Hide
FrmManu.Show
End If
If IsError(V) = False Then
With Me.CbxProd
.Clear
For Each R In Worksheets("ProCode").Range("A2:A1000")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount 0 Then
.ListIndex = 0
End If
End With
End If

End Sub

Private Sub UserForm_Initialize()
CbxMfg.RowSource =
Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True)
CboFire.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboHealth.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboReact.RowSource =
Worksheets("Lists").Range("D2:D5").Address(externa l:=True)
CboDisp.RowSource =
Worksheets("Lists").Range("E2:E4").Address(externa l:=True)
CboDept.RowSource =
Worksheets("Lists").Range("C2:C10").Address(extern al:=True)

End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VB Code Issues Bob1866 Excel Discussion (Misc queries) 1 August 30th 09 11:58 PM
Help with code issues Mekinnik Excel Programming 0 October 31st 07 03:51 PM
Help with code issues Mekinnik Excel Programming 2 October 16th 07 01:34 PM
C# VBA DLL issues Temporalis Excel Programming 5 October 20th 06 10:49 PM
backward compatibility code issues Mark Kubicki Excel Programming 1 October 10th 03 07:37 PM


All times are GMT +1. The time now is 04:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement