By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Validation not working: run-time error '-2147417848 (80010108)'

P: 11
I'm trying to get certain cells to show a hovering alert message when I click on them. (I don't want an error-message style box to pop up, because I'll eventually want it to show for lots of cells at once.)

I recorded a macro to get the code, which I tweaked minimally, and pasted into a clean spreadsheet to test it. It seems to work beautifully. However, when I try to apply the same code within a larger programme, in a working spreadsheet, I get the following error-message:
Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disonnected from its clients.
(If I click "debug", then Line 15 in Code Sample 2 below is highlighted)



Code Sample 1 - the version that works

Expand|Select|Wrap|Line Numbers
  1. Sub ValidationTesting()
  2.  
  3. Dim Row As Long
  4. Row = 10
  5.  
  6.             With Worksheets(1).Cells(Row, 6).Validation
  7.                     .Delete
  8.                     .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
  9.                     .IgnoreBlank = True
  10.                     .InCellDropdown = True
  11.                     .InputTitle = "Low Calcium Concentration!"
  12.                     .ErrorTitle = ""
  13.                     .InputMessage = "Ca concentration was too low for the calculation.  It has been adjusted to the minimum value allowed."
  14.                     .ErrorMessage = ""
  15.                     .ShowInput = True
  16.                     .ShowError = True
  17.             End With
  18.  
  19. End Sub
  20.  
Code Sample 2 - the version that doesn't work

Expand|Select|Wrap|Line Numbers
  1. Private Sub CaAdjustmentButton_Click()
  2.  
  3. Dim TotalRows As Long
  4. TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
  5. Dim Row As Long
  6.  
  7. For Row = 10 To TotalRows Step 1
  8.  
  9.         If Worksheets("Calculation").Cells(Row, 5) < 5 Then
  10.                 Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 3 'red
  11.                 Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 2 'white
  12.                 Worksheets("Calculation").Cells(Row, 6).Value = "5"
  13.                 With Worksheets("Calculation").Cells(Row, 6).Validation
  14.                     .Delete
  15.                     .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
  16.                     .IgnoreBlank = True
  17.                     .InCellDropdown = True
  18.                     .InputTitle = "Low Calcium Concentration!"
  19.                     .ErrorTitle = ""
  20.                     .InputMessage = "Ca concentration was too low for the calculation.  It has been adjusted to the minimum value allowed."
  21.                     .ErrorMessage = ""
  22.                     .ShowInput = True
  23.                     .ShowError = True
  24.                 End With
  25.  
  26.             ElseIf Worksheets("Calculation").Cells(Row, 5).Value > 150 Then
  27.                 'Call CaAboveMaximum(Row)
  28.                 Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 5 'blue
  29.                 Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 2 'white
  30.                 Worksheets("Calculation").Cells(Row, 6).Value = "150"
  31.             Else:
  32.                 'CaWithinRange (Row)
  33.                 Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 35 'pale green
  34.                 Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 1 'black
  35.                 Worksheets("Calculation").Cells(Row, 6).Value = Worksheets("Calculation").Cells(Row, 5).Value
  36.         End If
  37.  
  38. Next Row
  39.  
  40. Call InactivateButton
  41.  
  42. MsgBox ("Red or Blue cells indiate input values for Calcium outside allowable range.  Select individual cells for details.")
  43.  
  44. End Sub
  45.  
  46.  
Jan 24 '08 #1
Share this Question
Share on Google+
6 Replies


P: 11
Okay, following a bit more investigation - the problem ONLY happens when i try to run code sample 2 (see previous post, above) from a button. If I run it from a SheetChange event, or just by itself, it works perfectly.
Jan 25 '08 #2

Expert 5K+
P: 8,434

P: 11
I already saw that article, before I posted. Trouble was, I couldn't understand any of it :-(

A colleague of a friend has said he thinks the problem is to do with the use of validations, which are complicated (and use external code?). So now i'm trying to figure out how to use shapes with text in, instead of input messages.
Feb 15 '08 #4

kadghar
Expert 100+
P: 1,295
I already saw that article, before I posted. Trouble was, I couldn't understand any of it :-(

A colleague of a friend has said he thinks the problem is to do with the use of validations, which are complicated (and use external code?). So now i'm trying to figure out how to use shapes with text in, instead of input messages.
First of all, are you running your code from VBA in Excel, or from any other VB.
This error happens when you have a variable not defined, so it could be that you're outside Excel's VBA and it doesn't recognize the XL constants.
Feb 15 '08 #5

P: 11
First of all, are you running your code from VBA in Excel, or from any other VB.
This error happens when you have a variable not defined, so it could be that you're outside Excel's VBA and it doesn't recognize the XL constants.
hi kadghar - i'm running the code within excel.

to save you putting a lot of time into fixing this, though - i've decided to use AddComment instead, it's working fine and seems to do the job. However, if you have any insight into the problem and want to share it, then please feel free - it's all good learning, for me.
Feb 15 '08 #6

P: 1
Hello,

I had the same problem (Run-time error '-2147417848 (80010108)') when I was working in VBA Excel with validation checks.

I solved this selecting the cell where the validation is (range.select) before applying the "validation.add". With this correction, the macro works fine.

In your example, I suppose you will need that:


.....
.....
Worksheets("Calculation").Cells(Row, 6).Value = "5"
Worksheets("Calculation").Cells(Row, 6).Select
With Worksheets("Calculation").Cells(Row, 6).Validation
.....
.....



Regards
Feb 1 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.