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

 

[NdNoviceHlp]

Can you replace the combobox with a listbox? Maybe delete the combobox. Save and then add a new one to the userform. There's something about comboxes on userforms and memory leak as well... probably irrelevant. Anyways, good luck and post back if you have any success. Dave

 

[Beean]

I tried what you suggested to no avail, the code still stops at the same point and won't finish executing after it copies the first set of data over... I even rebuilt/ coded my userform as a second test from your suggestion...what is there with a memory leak?

 

[LEE355]

Hi,

You can't use the Me keyword in an event outside of the code behind the UserForm module.
You can only use it in an event that is named UserForm_Click, UserForm_Initialize, etc.
So you can't use it in the code behind a CommandButton.
Change all instances of Me with "frmRejCode" without the quotes.

For example, change the line:

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

to read:

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

 

[LEE355]

Just wanted to add that are many ways you can use the Me keyword outside of the form module. But in your case, you
seem to be using the Me to refer the CommandButton itself.

I made a mistatement saying you can not ever use the Me keyword outside of the userform module.

 

[NdNoviceHlp]

LEE355 your solution....

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

is the same as...

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

which didn't seem to work before? Puzzling it is. Dave