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.