ColumnHidden Property - Doesnt seem to hide

[Tony2021]

Hello,

is the below the correct code to turn on/off the columnhidden in a datasheet? 

Me.txtProjName2.ColumnHidden = True

Me.txtProjName2.ColumnHidden = False

I have opened the form and right clicked txtProjName2 and hide the field manually to start off with but I am not sure if I am to do that initially but I tried both ways (rt click fld and checking the field for hide or unchecking it for not hiding it). 

The code doesnt seem to work all the time though (only worked 1x out of 20).  I am not sure if I need to turn on/off another setting or setfocus (I read about this but not sure if necessary)?  I dont think its the visible setting.  Maybe there also needs to be additional code elsewhere? in the forms On Load?  I dont know. 

thank you very much

Sorry If this was posted 2x.  I dont see it in the list. 

[#2]

@Tony2021 

That should be effective, but we probably don't have enough information to be clear about what else might be going on. 
I use this code to hide columns I want to remain hidden and to set other columns to fit the exact width of the visible text in those columns. I run it from the Current event of the form. It has the same syntax you mentioned.

==========
For Each ctl In frm.Controls
If ctl.Tag = "Hidden" Then
If ctl.ColumnHidden = False Then ctl.ColumnHidden = True
ElseIf ctl.Tag = "Fixed" Then
ctl.ColumnWidth = -2 ' -2 sets column width to fit displayed text exactly
Else
'Future use?
End If
Next ctl
frm.RowHeight = 0.1667 * 1440 ' set rows to default height

===========

Can you isolate the times when it is effective as opposed to when it isn't? It's true that events can interact in a dynamic process.

[#3]

@George Hepworth 

Hi George,

thank you for the response. I seem to get a Object Required when the form opens (please see pic below).  I would like to test to see if it is what I need. 

thank you. Happy New Year!

[#4]

Sorry I didn't intend for that to be used as is. I posted it as an example of a procedure I use that works with the ColumnHidden property the same way you did.
I should have included the entire Sub. I'll do that. However, if it isn't working in your code, it may not be effective either.

[#5]

@Tony2021 

Here's a full Function. 

Public Function DataSheetControls(ByRef frm As Form) As Variant
'---------------------------------------------------------------------------------------
' Procedure : DataSheetControls
' Author : GPC Data
' Date : 9/25/2010
' Purpose :
'---------------------------------------------------------------------------------------

On Error GoTo errHandler

Dim ctl AS Control

For Each ctl In frm.Controls
If ctl.Tag = "Hidden" Then
If ctl.ColumnHidden = False Then ctl.ColumnHidden = True
ElseIf ctl.Tag = "Fixed" Then
ctl.ColumnWidth = -2 ' -2 sets column width to fit displayed text exactly
End If
Next ctl

frm.RowHeight = 0.1667 * 1440 ' set rows to default height

exitProc:

Exit Function

errHandler:

MsgBox Err & " " & Err.Description

Resume Cleanup

End Function

Call it like this:

Private Sub Form_Current()

     Call DataSheetControls(Me)

End Sub

[#6]

@George Hepworth 

Hi George,

thank you. I seem to get a 'label not defined" msg box on RESUME CLEANUP.  

I put the code in my module and calling from the current event on the form as detailed above. 

Let me know if I am not following. 

thanks! 

[#7]

You could experiment a bit with the syntax (one of the best trouble-shooting methods I know), although I did miss cleaning that up when I redacted the full procedure to remove some unnecessary elements for this example. The name of the label is exitProc:, not Cleanup

[#8]

@George Hepworth 

Hi George.  Thank you for the correction.   Question:  Is the code suppose to hide certain fields or applies to all fields that are hidden (rt clicked field and chose hide).  

I need to hide only 1 field on my split form.  I think I am doing something wrong but from what I have read all you do is make a button and put the below code on it.  I thought maybe my form was corrupted and I created another form and put 2 buttons on it (1 for hide, 1 for unhide) with the code but nothing happens.  The field doesnt hide or unhide even after I manually assign it as hidden by rt click thinking its something to do with the initial setting when form opens.  

What do you think I am doing wrong?  The below code seems simple enough so I narrow it down to how I am using it or there is something missing.  Can you create a form and hide unhide with the below code? 

Private Sub txthide_Click()

     Me.txtProjectName.ColumnHidden = True
End Sub

Private Sub txtUnhide_Click()

     Me.txtProjectName.ColumnHidden = False
End Sub

[#9]

@Tony2021 It should impact only controls with one of the two tags specified. (The tag has to match exactly.)

By the way, I also seem to have misinterpreted the original question. You specified a datasheet in the original question.

However, in this answer you mention a split form, in which there is both a datasheet section and a "normal" section. I must have missed that before.

I am not sure that I've ever tried something like this in a split form. I'm sure it would not impact the normal view section of the form, even if it does work with the datasheet section.... More testing required.

[#10]

George, I have found the answer. The previously posted codes do not work on a split form. Only the below works for a split form. I searched quite a bit online. Many websites do not state that a split form requires different codes. Anyways, I am using the below and it works great. thank you for the assistance. I can use the other codes on another form to auto fit the fields.

Private Sub txthide_Click()
'Me.txtProjectName.ColumnHidden = True 'this doesnt work on a split form
Screen.ActiveDatasheet.txtProjectName.ColumnHidden = True
End Sub

Private Sub txtUnhide_Click()
'Me.txtProjectName.ColumnHidden = False 'this doesnt work on a split form
Screen.ActiveDatasheet.txtProjectName.ColumnHidden = False
End Sub

[#11]

I'm glad to hear you worked it out. Continued success with the project.