Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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: 1989) 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