Run time error -2147417848 (80010108) Method "_range" of object "_worksheet" failed.

[aoxolom]

Hi All,

New to this forum and VBA. I am currently making a calculator and it was working fine for sometime but now I am receiving an error "Runtime error -2147417848 (80010108) method _range of object _worksheet failed". Where as the only command that is running is to display a number from a cell into the textbox.
Major portion of my work is completed and this thing is now causing a heck of a problem for me and I am really sick and tired of dealing with this. Any type of help will be appreciated.


Regards,
Aoxolom


Following is code attached:

Private Sub TextBox8_Change()

IF KeyAscii > Asc("9") Or KeyAscii < Asc("0") Then
          If KeyAscii = Asc("-") Then
                If InStr(1, Me.TextBox8.Text, "-") > 0 Or _
                 Me.TextBox8.SelStart > 0 Then KeyAscii = 0
            ElseIf KeyAscii = Asc(".") Then
                 If InStr(1, Me.TextBox8.Text, ".") > 0 Then KeyAscii = 0
            Else
                KeyAscii = 0
          End If
End If
    
 If IsNumeric(Application.Sheets("Main Calculator").Range("D19").Value) = False Then '(cause of error)
        TextBox8.Text = "INVALID"

Else
         TextBox8.Value = Application.Sheets("Main Calculator").Range("D19").Value
         TextBox8.Value = Round(TextBox8.Text, 3)
End If

End Sub

[SamT]

"Application.Sheets" refers to the Active workbook. Is the correct workbook active? Can you replace "Application.Sheets" with "Workbooks(BookName).Sheets(...."

[aoxolom]

Thank you for the reply SamT.
I changed the value for it but the error still continues. The thing is that the program runs for the 1st time in some cases but after resetting it the error pops up again.

[SamT]

Where is KeyAscii coming from? It doesn't seem to be a VBA key word.

Also the Change Event occurs aftweer the KeyUp eent, so it's too late to capture the pressed Key's value.

Try something like this

Private PreviousTextBox8Value As String

Private Sub TextBox8_Change()
Dim LastChar As String

With Me
  LastChar = Right(.TextBox8, 1)
  If Not IsNumeric(LastChar) Then
          If Asc(LastChar) = Asc("-") Then
                If InStr(1, .TextBox8.Text, "-") > 0 Or _
                 .TextBox8.SelStart > 0 Then TextBox8 = PreviousTextBox8Value
            ElseIf LastChar = "." Then
                 If InStr(1, .TextBox8.Text, ".") > 0 Then .TextBox8 = PreviousTextBox8Value
            Else
                .TextBox8 = PreviousTextBox8Value
          End If
  Else: PreviousTextBox8Value = .TextBox8
  End If
End With
'
'More code
End Sub

Just be sure you reset PreviousTextBox8Value to "" When done

[aoxolom]

Again thanks for the reply Sam.

I changed the declaration of textbox8 from "change" to "afterupdate" and the code seems to run flawless now. Thanks for all the help and will bother you again if the issue comes up.

Regards,
aoxolom

[RajivS]

Hi Guys

I also need help with a Run-time error '-2147352571(80020005)': Type Mismatch

I can't figure out how to fix this issue. My Script is as follows :

Private Sub CommandButton1_Click()


Dim con As Variant
Dim cmd As Variant
Dim StoredProc As String
Dim WSP1 As Worksheet
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")


Set parm = cmd.CreateParameter("(atsymbol)ChosenBDM", adVarChar, , 4, TextBox1.Value())
Set parm2 = cmd.CreateParameter("(atsymbol)NewBDM", adVarChar, , 4, TextBox2.Value())


Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."


    ServerName = "----" ' Enter your server name here
    DatabaseName = "----" ' Enter your database name here
    UserID = "--" ' Enter your user ID here
    Password = "----" ' Enter your password here
    StoredProcedure = "[New_BDM_Creation]" ' Enter Stored Procedure here


' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" & UserID & ";Password=" & Password & ";Trusted_Connection=no"
cmd.ActiveConnection = con


Application.StatusBar = "Running stored procedure..."


    cmd.CommandTimeout = 900
    Set cmd = cmd.Execute(parm, parm2, StoredProcedure) ------> Error appears here


Set cmd = Nothing
con.Close
Set con = Nothing


Application.StatusBar = "Data successfully updated."


End Sub

[Paul_Hossler]

It's more effective to start a new topic instead of just tagging on to a previous one, even if you think that they're somewhat related