Userform only only partially completing in Excel 2003

[Beean]

To start thank you for taking a look at this...

I am completely self-taught and have visited this forum numerous times to get insight into what I am trying to accomplish, but I have been stuck on the following for some time now.

I am trying to create a Userform which will copy the data entered into a specific worksheet to be compiled through normal excel functions. This Userform should be called anytime a particular selection is made on a dropdown from another sheet.

So the experiance is essentially that the user selects "PYMT rejection" in the dropdown box (located in column 28 "AB") and the userform should show. The user enters in the appropriate data and then clicks the command button which copies the entered data to the data sheet "Rejection Codes" and then closes the userform.

My problem is that the userform will execute flawlessly when Testing in the VBA editor, but once my macro calls the userform up it will only copy the data from the 1st field and will not close the userform after. Additionally, there is another computer at work running excel 2000 and there is no problem with the call macro or the userform when run...

Below is the entirety of the code being used currently. Please let me know what else I can provide, I am just stumped.

Userform(Named "frmRejCode")
Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Rejection Codes")
'''find first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.txtRejection.Value) = "" Then
Me.txtRejection.SetFocus
MsgBox "Please enter a rejection Code"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtRejection.Value
ws.Cells(iRow, 2).Value = Me.cboRes.Value
'clear the data
Me.txtRejection.Value = ""
Me.cboRes.Value = ""
Me.txtRejection.SetFocus
Unload frmRejCode
End Sub



Call Macro (Resides in same worksheet as dropdown boxes)


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 28 Then
If ActiveCell.Text = "PYMT Rejection" Then
Call frmRejCode.Show
End If
End If
End Sub

 

[Dryver14]

What is this?

ws.Cells(iRow, 2).Value = Me.cboRes.Value

 

[Beean]

What is this?

ws.Cells(iRow, 2).Value = Me.cboRes.Value

I belive (if i did it right) it should refer to the dropdown list in the Userform, named "cboRes"...so this string is taking the current value of the dropdown and copying it to the data sheet in the same row as the previous value but in column 2 "B".

 

[Beean]

SO since you brought that up I've been looking at it more...running the userform through the editor it takes the information from both the txt field (me.txtRejection.value) and the combo box (me.cboRes.value) and paste it into the 1st empty row on my data sheet in columns 1 and 2 respectively. However, when run through my call macro, it will only execute the first of that code before stopping...it doesn't complete anything after the first line (even when they are switched).


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtRejection.Value
The break is here, when run through call macro will only complete until line above and will not finish execution...but when tested runs fine...
ws.Cells(iRow, 2).Value = Me.cboRes.Value
'clear the data
Me.txtRejection.Value = ""
Me.cboRes.Value = ""
Me.txtRejection.SetFocus
Unload frmRejCode
End Sub

 

[NdNoviceHlp]

Does Me.cboRes.Value have any value .ie has anything been selected?
Trial a msgbox Me.cboRes.Value to find out. HTH. Dave

 

[Beean]

Does Me.cboRes.Value have any value .ie has anything been selected?
Trial a msgbox Me.cboRes.Value to find out. HTH. Dave

It seems to have a value if the msg box is placed before
ws.Cells(iRow, 1).Value = Me.txtRejection.Value

But anything after does not have value...could it be that the userform is creating a new instance of itself once the data from that first line is copied? If so how could I code around it?

 

[NdNoviceHlp]

Maybe trial...

ws.Cells(iRow, 1).Value = frmRejCode.txtRejection.Value
ws.Cells(iRow, 2).Value = frmRejCode.cboRes.Value

The userform won't recreate itself. Dave

 

[Beean]

Maybe trial...

ws.Cells(iRow, 1).Value = frmRejCode.txtRejection.Value
ws.Cells(iRow, 2).Value = frmRejCode.cboRes.Value

The userform won't recreate itself. Dave

The first line of code still works, second does not. I also tested using a button to call the userform (just a click frmRejCode.show button) and the macro again works fine...so it might be some interaction with my call macro (which is really a combination of two seperate ws_change macros) and this particular code pair...

 

[NdNoviceHlp]

I really don't get it. The combobox cboRes is located on the userform isn't it? Dave

 

[Beean]

Yeah, the combobox is on the userform with (Waived, Drafted and negotiated) as the options. This is driving me nuts too = /, thanks for your ideas so far though, there were definitely not thigns I'd thought of yet.