MacroProblem-Receiving a Compile Error on UserForm

[cgeorge4]

Good Evening Anyone,

I created a UserForm with the categories I require by adding 8 TextBoxes, 8 ListBoxes, and 2 Command buttons.

I ran the code for this UserForm directly from the Project window and the form appears in Excel perfectly. I can add data to the 8 fields that I've set up.

There is a problem when I click the 1st CommandButton which is called "Add this Adjustment". I get the following error message:

Compile Error Method or data member not found

The following portion of my code gets highlighted (not yellow but blue) and only on ".Value"

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtSupplierID.Value

Please note that the "SupplierID" text box is named txtSupplierID

DETAILS:
File name is "Manual Adjustment Form"
Sheet name is "Manual Adjustment Form"
There is only that one sheet in the workbook

PURPOSE:
I want to use this UserForm to Add data to a blank Excel sheet and build the "database" from my entries - not to lookup from or pull from any existing database.


HERE IS MY 3 CODES IN FULL:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Manual Adjustment Form")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtSupplierID.Value
ws.Cells(iRow, 2).Value = Me.txtPortfolio.Value
ws.Cells(iRow, 3).Value = Me.txtPropertyName.Value
ws.Cells(iRow, 4).Value = Me.txtCycle.Value
ws.Cells(iRow, 5).Value = Me.txtCycleDate.Value
ws.Cells(iRow, 6).Value = Me.txtNotification.Value
ws.Cells(iRow, 7).Value = Me.txtRequester.Value
ws.Cells(iRow, 8).Value = Me.txtAdjustment.Value

'clear the data
Me.txtSupplierID.Value = ""
Me.txtPortfolio.Value = ""
Me.txtPropertyName.Value = ""
Me.txtCycle.Value = ""
Me.txtCycleDate.Value = ""
Me.txtNotification.Value = ""
Me.txtRequester.Value = ""
Me.txtAdjustment.Value = ""
Me.txtSupplierID.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserFormShow()
FrmManualAdj.Show False
End Sub


Thank you in advance for your help,

Juicy,

 

[repairman615]

Hello,

This may be worth a shot,

change:
ws.Cells(iRow, 1).Value = Me.txtSupplierID.Value

ws.Cells(iRow, 1).Value = Me.txtSupplierID.Text

 

[cgeorge4]

Hi,

Thank you for responding so quickly.

I changed Value to Text and I still received the same error message.

Any other ideas?

Thanks,

Juicy

 

[repairman615]

Hello,

I can reproduce this error if the textbox name is not exactly spelled correctly. (highlighted in blue)



I know you mentioned that the name txtSupplierID was correct, however ddouble check this for good measure.

Value or Text worked for me. My previous post was def not effective.

 

[cgeorge4]

Hi,
I just wanted to let you know that I worked out my ".Value problem.

Here is my final code and it works perfectly. I'm posting this here in case it might be helpful to someone else.

Below are 4 codes that I combined for my purpose

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Manual Adjustments")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtSupplierID.Value
'status of Portfolio & Property ID field as a comment temporarily
'ws.Cells(iRow, 2).Value = Me.txtPortfolio.Value
'ws.Cells(iRow, 3).Value = Me.txtPropertyID.Value
ws.Cells(iRow, 4).Value = Me.txtCycle.Value
ws.Cells(iRow, 5).Value = Me.txtCycleDate.Value
ws.Cells(iRow, 6).Value = Me.txtNotification.Value
ws.Cells(iRow, 7).Value = Me.txtAdjustment.Value
ws.Cells(iRow, 8).Value = Me.txtSpecialNotes.Value
ws.Cells(iRow, 9).Value = Me.txtSpecialHandling.Value
'clear the data
Me.txtSupplierID.Value = ""
Me.txtPortfolio.Value = ""
Me.txtPropertyID.Value = ""
Me.txtCycle.Value = ""
Me.txtCycleDate.Value = ""
Me.txtNotification.Value = ""
Me.txtAdjustment.Value = ""
Me.txtSpecialNotes.Value = ""
Me.txtSpecialHandling.Value = ""
Me.txtSupplierID.SetFocus
End Sub

Sub SHOWManualAdjustments()
frmManualAdjustments.Show
End Sub


Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close button!"
End If
End Sub