OLEObjects method doesn't work

0

Hi all. I'm working on a Excel VBA project. In Sheet1 i have a table with 3 command buttons for each row. Outside this table i have another command button named "cbSwap". When i click to this button, it opens a user form. This user form have 2 text boxes and a button. The user form takes two values from two text boxes and transmits them to a function named SwapTwoRange. This function takes the values, uses them as Excel range names. Here's the function:

Function SwapTwoRange(val1 As String, val2 As String)
    Dim arr1 As Variant, arr2 As Variant
    Dim Rng1 As Range, Rng2 As Range
    
    Set Rng1 = ThisWorkbook.Sheets("Sheet1").Range("row" & val1)
    Set Rng2 = ThisWorkbook.Sheets("Sheet1").Range("row" & val2)
    Application.ScreenUpdating = False
    arr1 = Rng1.Value
    arr2 = Rng2.Value
    Rng1.Value = arr2
    Rng2.Value = arr1
    Application.ScreenUpdating = True
'No problem occured until here.
    If (WorksheetFunction.CountA(Rng1) = 0) Then
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbStartRow" & val1).Object.Enabled = True
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbEndRow" & val1).Object.Enabled = False
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbClearRow" & val1).Object.Enabled = False
    Else
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbStartRow" & val1).Object.Enabled = False
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbEndRow" & val1).Object.Enabled = True
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbClearRow" & val1).Object.Enabled = True
    End If
    
    If (WorksheetFunction.CountA(Rng2) = 0) Then
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbStartRow" & val2).Object.Enabled = True
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbEndRow" & val2).Object.Enabled = False
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbClearRow" & val2).Object.Enabled = False
    Else
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbStartRow" & val2).Object.Enabled = False
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbEndRow" & val2).Object.Enabled = True
        ThisWorkbook.Sheets("Sheet1").OLEObjects("cbClearRow" & val2).Object.Enabled = True
    End If
End Function

cbStartRow, cbEndRow and cbClearRow are the command buttons in the table rows in Sheet1. When if block executes, they seem as they are enabled/disabled as i want. But when i select (for example) a disabled command button, and look at its properties, its Enabled property is True. And the program doesn't work as i want. I must refer to command buttons names with the values entered to text boxes (for example if textbox1's value is "1" <val1="1">, the first if-else block will change cbStartRow1, cbEndRow1 and cbClearRow1's Enabled property.)

 How can i do it without this problem? I have been searching for a solution in internet forums for last 8 days, but i couldn't find a correct solution. Please help. Thanks anyway.

Answer
Discuss

Answers

0

Try:

Application.WorksheetFunction.CountA (Rng1)

instead of:

WorksheetFunction.CountA(Rng1)
Discuss

Discussion

Thanks 4 your reply. But nothing changes. The command buttons seem like enabled (or disabled) in worksheet but when in design mode i select the button and open its properties window, i see it's enabled property is "disabled" (or enabled for second state). And this occurs only if i want to swap a blank row and a non-blank row (when if-else code block executes).
newbie Aug 1, '16 at 6:29 am
Ok then edit your original question and upload a sample file. Otherwise, I would say to store the "enabled" and "disabled" value in another way.
don (rep: 1492) Aug 1, '16 at 12:34 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login