[Solved] Hide Columns based on cell Value

[PMI_IT]

We have many forms the we are converting from Microsoft Excel to Open Office Calc. Macros are causing us issues the main one being hiding columns based on cell value. Is this possible in OO?

[squenson]

It is possible with a macro, but not with a formula. A formula cannot change the layout of the sheet and the only function that allows to dynamically change the style of a cell is called... STYLE().

[king_026]

Something like this quite silly macro i just made that hides colum A if cell E4 is 2?

Code: Select all

sub hide
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

flt = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("E4").Value


if flt = 2 then

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())

End if

end sub

[PMI_IT]

Thanks. The sub hide worked great. We actually need the macro to move through a series of cells check for value then hide the column if value = 0. Example: The Macro would need to check cell A1, if value = 0 hide column A, move on to Cell B1, if value = 0, hide column B etc. Checking values in more than one cell and hiding that column if value = 0. How would this macro be written?

[king_026]

here i modified it so that it will hide all the columns left of the current selection with a 0 in the first row

Code: Select all

sub hide
	rem ----------------------------------------------------------------------
	rem define variables
	dim document   as object
	dim dispatcher as object
	rem ----------------------------------------------------------------------
	rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	
	rem get the current column
	nCol = ThisComponent.CurrentSelection.CellAddress.Column

	rem set the properties for moving right
	dim args2(1) as new com.sun.star.beans.PropertyValue
	args2(0).Name = "By"
	args2(0).Value = 1
	args2(1).Name = "Sel"
	args2(1).Value = false

	rem make thecurrent column counter 
	dim cCol as integer
	CCol = 0
	
	rem goto the first column
	dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "ToPoint"
	args1(0).Value = "$A$1"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
	
	rem loop until you get back to the selected cell
	Do Until cCol > nCol
		
		rem hide if the cell value is 0
		if ThisComponent.CurrentSelection.string <> "" and ThisComponent.CurrentSelection.value = 0 then
	
			rem ----------------------------------------------------------------------
	 		dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
	
		End if
		
		rem goto the right nad increment the column counter
		dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
		cCol = cCol + 1 
	
	Loop

End sub

[PMI_IT]

That did it... Works great. Thank You