Macro hide/unhide columns dropdown list

[rwieczor84]

Hi! I hava a problem with macro. Can you help me please?
I need to hide / unhide columns depending on dropdown list value in cell B2.

error message is: BASIC runtime error. ‘91’ Object variable not set.

In this row:

If Target.Column = 2 And Target.Row =
3 Then

Hole macro looks like this:

Option VBASupport 1
Option Compatible

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Row = 2 Then
        If Target.Value = "No" Then
            Application.Columns("C:I").Select
            Application.Selection.EntireColumn.Hidden = True
        ElseIf Target.Value = "Yes" Then
            Application.Columns("C:I").Select
            Application.Selection.EntireColumn.Hidden = False
        End If
    End If
End Sub

[JohnSUN]

Try this

Option Explicit 

Sub CellB2WasChanged(oEvent As Variant)
Dim oSheet As Variant
	If oEvent.supportsService("com.sun.star.sheet.SheetCellRange") Then
		oSheet = oEvent.getSpreadsheet()
		oSheet.getCellRangeByName("C1:I1").getColumns().IsVisible = _
			UCase(oSheet.getCellByPosition(1, 1).getString())="YES"
	EndIf 
End Sub

For Calc to execute the macro when data in a sheet changes, assign it to a sheet event Content changed

[rwieczor84]

Yes! And now it work! Thanks!
Now I am wondering what I need to change if dropdwond list wasn’t B2 Cell but C3?

[JohnSUN]

Parameters of .getCellByPosition() are <column_num_from_0> and <row_num_from_0>. So, B2 will be (1, 1) as in my code, C3 will be (2,2) etc.

[rwieczor84]

Unfortunately I get a different Error: BASIC runtime error. Argument is not optional