Excel VBA Run-Time13 error

[Pianoman23]

Can anyone help with this error. The code I'm using is as follows:

Private Sub CommandButton1_Click()
DrugUserForm.Show
End Sub


Sometimes it works; sometimes it produces a Run-tine '13' error. Type mismatch.

It's driving me mad!!!

Any thoughts?

 

[Rick Rothstein]

Can anyone help with this error. The code I'm using is as follows:

Private Sub CommandButton1_Click()
DrugUserForm.Show
End Sub


Sometimes it works; sometimes it produces a Run-tine '13' error. Type mismatch.

That error is a "Type mismatch" error... I cannot think of any way the code you are showing us could produce that error. Are you sure your code is stopping in the code you showed us and not in one in the Initialize or Activate events for the UserForm?

 

[Pianoman23]

That error is a "Type mismatch" error... I cannot think of any way the code you are showing us could produce that error. Are you sure your code is stopping in the code you showed us and not in one in the Initialize or Activate events for the UserForm?

Thanks Rick.

It was definitely identifying the error at the point "DrugUserForm.Show.

I looked at the Initialize code and discovered that it just used UserForm as the name; so I changed this to "DrugUserForm" and the error didn't occur; however, all I get now is a blank form.

What should happen is that when the CommandButton is pressed, the details of whatever product is highlighted should show in the userform.

The first part of the form's code is as follows:

Private Sub DrugUserForm_Initialize()
Call GetTextBoxData
End Sub


Private Sub GetTextBoxData()
Dim v As String
Dim r As Integer
Dim DTBP As Currency
Dim DTWP As Currency
Dim diff As Variant




r = ActiveCell.Row
If Sheet6.Cells(r, "G").Value > 0 Then
dt = Sheet6.Cells(r, "G").Value
Else
dt = 0
End If


If Sheet6.Cells(r, "A").Value > "" Then
tbPSLCode.Text = Sheet6.Cells(r, "A").Value
End If
If Sheet6.Cells(r, "B").Value > "" Then
tbPipCode.Text = Sheet6.Cells(r, "B").Value
End If

I'm quite new to this so wonder if I'm missing something that's stopping the form from pulling in the data from sheet6.

 

[Andrew Poulsom]

The Initialize event procedure doesn't contain the name of the Userform. It's always:

Private Sub UserForm_Initialize()

In the Visual Basic Editor choose Tools|Options and check 'Break in Class Module' on the General tab. If you get an error you will now see the line that caused it. I suspect that you need to declare r as Long rather than Integer.

 

[Pianoman23]

The Initialize event procedure doesn't contain the name of the Userform. It's always:

Private Sub UserForm_Initialize()

In the Visual Basic Editor choose Tools|Options and check 'Break in Class Module' on the General tab. If you get an error you will now see the line that caused it. I suspect that you need to declare r as Long rather than Integer.

Thanks Andrew. I changed the name back to "UserForm" and changed r to long. I also ticked the "Break in Class Module"

Now, the first time I ran the procedure it worked. The second time I got the error in the following code:

If Sheet6.Cells(r, "Z").Value > "" Then
v = Sheet6.Cells(r, "Z").Value
tbTeva.Text = FormatCurrency(v, 2)
d = v - DTWP
tbTevDiff.Text = FormatCurrency(d)
End If


If Sheet6.Cells(r, "AA").Value > "" Then
v = Sheet6.Cells(r, "AA").Value
tbOTC.Text = FormatCurrency(v, 2)
d = v - DTWP
tbOTCDiff.Text = FormatCurrency(d)
End If

The break occured in the line tbOTC.Text = FormatCurrency(v, 2), which seems strange as it's the same as the preceding block.

 

[Andrew Poulsom]

What is assigned to v when you get the error?

 

[Pianoman23]

What is assigned to v when you get the error?

Using the watch window it says that v = "".

v is originally dimmed as String; however, the values it's being assigned are all currencies. Could this be affecting it? Should the line read
If Sheet6.Cells(r, "AA").Value > 0 Then
v = Sheet6.Cells(r, "AA").Value
tbOTC.Text = FormatCurrency(v, 2)

Basically what I'm trying to achieve (without error) is if the cell (in this case row 11, column AA) is greater than blank then assign the value to v and display it in the textbox in currency format. If the cell in column AA is blank then skip to the next IF statement.

Incidentally, the previous IF statement:
If Sheet6.Cells(r, "Z").Value > "" Then
v = Sheet6.Cells(r, "Z").Value
tbTeva.Text = FormatCurrency(v, 2)
d = v - DTWP
tbTevDiff.Text = FormatCurrency(d)
End If

doesn't cause the break even though the cell in column Z is blank; so I've no idea why it should break before the tbOTC.text line.

 

[Andrew Poulsom]

Is v is "" the cell is blank or contains a formula that returns "".

 

[Pianoman23]

Is v is "" the cell is blank or contains a formula that returns "".

Clearly there's something peculiar about column AA.

If I call the userform on a product with a price in column AA then all works fine; however, if I call on a product without a price in that column then I get the error.

I've highlighted the column and done a copy and paste special so that it only contains values and the error still occurs. Oddly, there can be blank cells in preceding columns that appear to have no effect and the userform works fine. I can't see what's happening in column AA to cause the problem.

As a test, I've taken one 'apparently' blank cell in column AA and done "clear contents". This has cured the problem - but I don't know what was in the cell as nothing showed up in the formula bar. The column was originally populated using a vlookup from another sheet; then copy and paste special to remove the formula and just leave the values. As this will be a monthly procedure across 5500 rows I don't want to have to go through every 'apparently' blank cell and clear contents. Is there a better way?

 

[Andrew Poulsom]

If a cell contains "" it's not blank and "" is greater than zero. Does this test work for you?

If IsNumeric(Sheet6.Cells(r, "AA").Value)