PDA

View Full Version : [SOLVED] Run time error -2147417848 (80010108) Method "_range" of object "_worksheet" failed.



aoxolom
04-28-2018, 03:20 AM
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
04-28-2018, 06:13 AM
"Application.Sheets" refers to the Active workbook. Is the correct workbook active? Can you replace "Application.Sheets" with "Workbooks(BookName).Sheets(...."

aoxolom
04-28-2018, 12:04 PM
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
04-28-2018, 12:55 PM
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
04-28-2018, 11:34 PM
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
06-27-2018, 11:22 PM
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
06-28-2018, 06:33 AM
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