Tbonekiller
Posts15Registration dateWednesday August 21, 2019StatusMemberLast seenJuly 20, 2022
-
Jan 13, 2020 at 09:23 AM
Tbonekiller
Posts15Registration dateWednesday August 21, 2019StatusMemberLast seenJuly 20, 2022
-
Feb 10, 2020 at 01:48 PM
So I am trying to make a spreadsheet that tracks our usage by diameter and individual serial number. I perform some basic checks using conditional formatting on the data entry page before moving the data to it's own page for tracking purposes. I wrote a macro to perform all the copy and paste, sorting, formula writing, etc... so I could move through the data entry quicker and then assigned it to a button.
The problem I am having is I get a
"runtime error -2147417848 (80010108)
automation error
The object invoked has disconnected from its clients"
Now the part I can't seem to figure out is that this locks up excel usually and I have to ctrl-alt-del to task manager and close the excel task before I can get back into excel. Now I can get this error once (sometimes 3 or 4 times even) or not at all other times, but once I'm back into excel I reenter the same information and it works fine. So I can't pinpoint it to a certain diameter or group of information being entered. I'll list the code below and I'm sorry if there is an obvious way to do this easily, but I'm new to all this. So thank you for any help you can provide.
Sub update_die_info()
'
' update_die_info
' updates all sheets based on value of c11
'
'
'
'highlights info to copy
Sheets("Check out Sheet").Select
Range("A11:h11").Select
Selection.Copy
'pulls info from "C11" to activate appropriate sheet to move data to
find_sheet
'inserts information onto appropriate sheet
Range("A6").Select
Selection.Insert Shift:=xlDown
Range("A6:H6").Select ----------------------------(locks up here when it does)
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("A6").Select
'see description below for each macro listed below
clear_conditional_format_mic_readings
sort_date
total_of_dies_ran
total_coils_produced
Sheets("Check out Sheet").Select
Range("b11:g11").Select
Selection.ClearContents
Range("A11").Select
Save
End Sub
Sub find_sheet()
'
'
' finds appropriate sheet based on info in "C11"
'
'
'finds sheet name you want based on "C11"
sheetname = ThisWorkbook.Worksheets("Check out Sheet").Range("c11").Text
ThisWorkbook.Worksheets(sheetname).Activate
End Sub
Sub clear_conditional_format_mic_readings()
'
' clear_conditional_format_mic_readings Macro
' clear conditional format for mic readings
'
' Run first
'
Range("E6:H6").Select
Selection.FormatConditions.Delete
Range("A6").Select
End Sub
Sub sort_date()
'
' sort_date Macro
'
' run second after clear_conditional_format_mic_readings
'
Range("A6:h2505").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("A6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A6:h2505")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A6").Select
End Sub
Sub total_of_dies_ran()
'
' total_of_dies_ran Macro
' finds how many dies have ran
'
' Run third after sort_date
'
ActiveSheet.Select
Range("B6:B2505").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-78
Range("K8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("K7:K2507").RemoveDuplicates Columns:=1, Header:= _
xlYes
Range("a6").Select
End Sub
Sub total_coils_produced()
'
' total_coils_produced Macro
' writes formula for total coils produced
'
' Run fourth after total_of_dies_ran
'
ActiveSheet.Select
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF(R[-2]C[-10]:R[2497]C[-10],RC[-1],(R[-2]C[-8]:R[2497]C[-8]))"
Range("L8").Select
Selection.AutoFill Destination:=Range("L8:L408"), Type:=xlFillDefault
Range("L8:L408").Select
ActiveWindow.SmallScroll Down:=-408
Range("A6").Select
End Sub
Sub Save()
'
' Save Macro
' Saves worksheet
'
'
ActiveWorkbook.Save
End Sub
Sub get_totals()
write_totals_by_month_formula
Hide_Rows
Save
End Sub
Sub Hide_Rows()
'
'this evaluates the information in column N and hides rows that have no coils produced and shows rows that have coils produced
'
BeginRow = 6
EndRow = 172
ChkCol = 14
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Sub write_totals_by_month_formula()
'
' write_totals_by_month_formula Macro
'
Tbonekiller
Posts15Registration dateWednesday August 21, 2019StatusMemberLast seenJuly 20, 2022 Jan 13, 2020 at 11:13 AM
I do using this part of the macro
Sub find_sheet()
'
'
' finds appropriate sheet based on info in "C11"
'
'
'finds sheet name you want based on "C11"
sheetname = ThisWorkbook.Worksheets("Check out Sheet").Range("c11").Text
ThisWorkbook.Worksheets(sheetname).Activate
End Sub
? are you saying I have to activate it again, I'm not understanding since I already activate that sheet
Everytime you change the active sheet you need to declare it prior to selecting the range. The sub clear conditional format mics reading never calls find sheet. Neither do any of the other subroutines or functions.
Just declare the sheet name in a variable as in
Dim sheetname as worksheet
Sheetname ="Check out Sheet"
Tbonekiller
Posts15Registration dateWednesday August 21, 2019StatusMemberLast seenJuly 20, 2022 Jan 13, 2020 at 01:43 PM
Yes, once I find what sheet to go to using the info in "c11" and activate it all the other functions happen on that sheet until I return to the original "check out sheet" to setup for next entry. So I do not understand why I need to list the sheet name each time I activate a new range when it's already on that sheet. Am I missing something here?
Do you have code in a module or on the worksheet? You may need to put it into a module that is common to both sheets. I ran the code fine when I placed the code into a module.
Annabelle_0855
Posts1Registration dateThursday January 2, 2020StatusMemberLast seenFebruary 10, 2020 Feb 10, 2020 at 06:14 AM
If you use buttons from the forms toolbar, it is much simpler in that you
can probably use a single routine to handle the click event for the same
buttons on all pages and you can simply assign
the macro by assigning it to the on action property of the button.
Tbonekiller
Posts15Registration dateWednesday August 21, 2019StatusMemberLast seenJuly 20, 2022 Feb 10, 2020 at 01:48 PM
Thank you everyone for your responses. Even though none of your suggestions worked for me in this particular instance, I did find a way to make it work on my own by reading some other issues people are having. I added this line into my code right after I find the sheet
Application.Wait (Now + TimeValue("0:00:01"))
This makes the instructions pause for 1 second after activating the sheet before carrying out any further instructions. This has been working for the past 2 weeks now with no further issues.
The only thing I can figure out is the process of finding the sheet based on the cell information takes awhile so the instructions were being missed and causing the error.
