Posts by thcheme

Quote from royUK

My code will check if specific cells are changed. With the information you provided that's all I can do. The selcet case needs extending to cover your other cells.

If the sheet is protected then you need to unprotect it within the code, then protect it. Otherwise, you can protect withuserinterface only.

Does it work on AB19?

Hello again,

I removed the Case 20 part but the code wasn't making any changes based on AB19, I don't know why.

Actually I changed the approach and made 10 drop down combo boxes and linked the references cell with a macro. Seems to work and I think less trouble than having a macro run in the background everytime someone makes any change.

Thanks for your help Roy

Quote from royUK

Maybe

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
Select Case Target.Address
Case "AB19"
If .Value = "User Defined" Then
Range("AG19:AM19").Font.ThemeColor = xlThemeColorDark1
Range("AN19:AO19").Interior.Color = 14803455
.Locked = False
Else
Range("AG19:AM19").Font.ThemeColor = xlThemeColorLight1
Range("AN19:AO19").Interior.Pattern = xlNone
.Locked = True
End If
Case "AB20"
' etc
End Select
End Sub

Display More

Thanks for the input.

I cant seem to make your code run as intended. I am a beginner VBA user, I just compile code i find over google haha.

Actually now I'm thinking this approach wont work since the sheet will be locked and password protected.

My understanding this code will constantly check if there is a match whenever anything on the sheet is modified. But if that's true I'm not sure how this will work with a password protected sheet since the code will do the format changes whether there is a match or not.

Hi all,

I have this simple code that is acting in the background to check if the data validation list selection in Cell AB19 is "User Defined". It all basically formatting changes.

Now this macro is only acting on Cell AB19 where this data validation list is. But I have 10 more different rows each having its own data validation list that will follow the same check.

I am trying to transform this macro to act not only on Cell AB19 but also to act on the 10 other data validation lists on Rows 20 to Row 30 (AB20, AB21, AB22 etc.). I don't want to repeat this macro 10 times to check for each data validation.

Is there a tidier way to adjust this macro. Or maybe any other suggested code.

Any help is appreciated.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Range("AB19").Value = "User Defined" Then
    Range("AG19:AM19").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
    End With
    Range("AN19:AO19").Select
    With Selection.Interior
        .Color = 14803455
    End With
    Selection.Locked = False
Else
    Range("AG19:AM19").Select
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
    End With
    Range("AN19:AO19").Select
    With Selection.Interior
        .Pattern = xlNone
    End With
    Selection.Locked = True
End If
End Sub

Thanks for the input Justin.

After many trials, if found out that error is occurring on this code line "Do Until Range("AB20").Value < 0.0001".

Whenever AB20 is evaluated as #NUM!, the macro stops and gives a type 13 error.

Is there anything I can add to this line that whenever it evaluates a #NUM! it goes to the error handler?

I tried "If Application.WorksheetFunction.IfError(ws.Range("AB20"), True) = True Then i = i + 0.1" but it doesn't seem to work.

Thanks.

Thanks for the reply Justin.

I apologize as I cannot post the worksheet due to work rules.

I will try to explain in details.

Cell C28 is input by the user. Lets say it has a value of 100.

Line 9 of the code will subtract " i " from 100 = 99.9. This is my first assumed value of AB9 for the first iteration.

Line 11 will divide AB9 by 2 = 49.95. This is my first assumed value of AB4 for the first iteration.

The worksheet can now solve itself using these values and generate values from formulas that are placed in cells AB17 and AB12.

These newly generated values in AB17 and AB12 are replaced in place of AB9 and AB4, respectively, and are the new assumed values of the second iteration (Line 15-24 of the code).

This process in looped until the error criteria is met which is placed in cell AB20 = ABS( (AB17-AB9) + (AB12-AB4)). Basically i want to match AB17 with AB9 and AB12 with AB4. This way the solution would have converged.

Now being that the formulas are complicated, sometimes they evaluate a #NUM! or #DIV/0! resulting in a type 13 mismatch error. What I want is whenever this error occurs, it will go to the error handler which adds 0.1 to i = 0.2 and go back to the beginning in which new values of AB9 and AB4 are placed.

Line 9 of the code will subtract " i + 0.1 " from 100 = 99.8. i + 0.1 due to the error handler code.

Line 11 will divide AB9 by 2 = 49.9.

and the whole process is repeated until AB20 is satisfied.

I hope this makes it clearer. Thanks for the help.

Hi all,

I am trying to compile a VBA that performs an iteration of two assumed variables. Basically I need to assume two temperatures, perform a series of calculations and then I get two new temperatures. these new temperatures are then replaced in the location of the first assumed temperatures and the process is repeated until the difference between the assumed and calculated temperatures is minimal.

I don't know how to write VBA from scratch, but I am able to compile based on Google searches of code. I have the following but I am running into an issue since the solution is not easily found. sometimes the assumed temperature leads the calculation to give a division by zero, or #NUM!, for that reason I get a type 13 mismatch. I need to include an error handler than changes the initial assumed temperature.

This code works the first iteration, but fails with a type 13 on the second. AB9 and AB4 are the first assumed temperatures. AB17 and AB12 are the calculated temperatures. AB20 is the error difference between assumed and calculated.

Appreciate the help.

Sub Iteration()

Dim i As Single

i = 0.1

Do
    Range("AB9").Select
    ActiveCell.Formula = "=C28 - " & i & ""
    Range("AB4").Select
    ActiveCell.Formula = "=AB9 /2"

On Error GoTo ErrorHandler:
    Do Until Range("AB20").Value < 0.0001
        Range("AB17").Select
        Selection.Copy
        Range("AB9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("AB12").Select
        Selection.Copy
        Range("AB4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    Loop
ErrorHandler:
i = i + 0.1
Loop
End Sub