|
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 -
Sub ValidationTesting()
-
-
Dim Row As Long
-
Row = 10
-
-
With Worksheets(1).Cells(Row, 6).Validation
-
.Delete
-
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
-
.IgnoreBlank = True
-
.InCellDropdown = True
-
.InputTitle = "Low Calcium Concentration!"
-
.ErrorTitle = ""
-
.InputMessage = "Ca concentration was too low for the calculation. It has been adjusted to the minimum value allowed."
-
.ErrorMessage = ""
-
.ShowInput = True
-
.ShowError = True
-
End With
-
-
End Sub
-
Code Sample 2 - the version that doesn't work -
Private Sub CaAdjustmentButton_Click()
-
-
Dim TotalRows As Long
-
TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
-
Dim Row As Long
-
-
For Row = 10 To TotalRows Step 1
-
-
If Worksheets("Calculation").Cells(Row, 5) < 5 Then
-
Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 3 'red
-
Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 2 'white
-
Worksheets("Calculation").Cells(Row, 6).Value = "5"
-
With Worksheets("Calculation").Cells(Row, 6).Validation
-
.Delete
-
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
-
.IgnoreBlank = True
-
.InCellDropdown = True
-
.InputTitle = "Low Calcium Concentration!"
-
.ErrorTitle = ""
-
.InputMessage = "Ca concentration was too low for the calculation. It has been adjusted to the minimum value allowed."
-
.ErrorMessage = ""
-
.ShowInput = True
-
.ShowError = True
-
End With
-
-
ElseIf Worksheets("Calculation").Cells(Row, 5).Value > 150 Then
-
'Call CaAboveMaximum(Row)
-
Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 5 'blue
-
Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 2 'white
-
Worksheets("Calculation").Cells(Row, 6).Value = "150"
-
Else:
-
'CaWithinRange (Row)
-
Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 35 'pale green
-
Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 1 'black
-
Worksheets("Calculation").Cells(Row, 6).Value = Worksheets("Calculation").Cells(Row, 5).Value
-
End If
-
-
Next Row
-
-
Call InactivateButton
-
-
MsgBox ("Red or Blue cells indiate input values for Calcium outside allowable range. Select individual cells for details.")
-
-
End Sub
-
-
| |
Share this Question
|
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.
| |
|
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.
| |
| 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.
| |
|
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.
| |
|
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
| | | |  Question stats - viewed: 10615
- replies: 6
- date asked: Jan 24 '08
|
