UserForm information and hidden column references

[Excelnoub]

Good morning,
Just wondering if this can be resolved. It seems that, when I have hidden columns in my sheet and doing a reference to a column that is hidden, i get an error "Run Time Error 13". But when I unhide the column everything is ok.

Does this mean I will have to Unhide then once my code runs on my userform, on unload to hide the column again? Is there a workaround with this?

Userform Code: (Sheet column M is hidden)

Please Login or Register  to view this content.

[Norie]

Works fine for me when column M is hidden as long as value in the active row of column M is numeric and I use Value instead of Text here.

Please Login or Register  to view this content.

[Excelnoub]

So weird, I searched online and looks like it’s a mystery. Some people close the workbook and re-open it, and presto. But I’ve been doing this several time and still getting the error. (Type Mismatch).

I do have a numeric in my Range("M" & ActiveCell.Row) but when hidden an error occur and when I unhide the column, it works perfect.

[romperstomper]

As Norie said, don't use .Text, use .Value

[Norie]

Did you try changing Text to Value as I suggested?

You only really use Text with cells when you want to get the value in the cell as it's formatted, eg if the cell had a date/currency value.

[Excelnoub]

Yeah well that is one of the problem... I have a format of "000" to two of these so will try to add format...

Now it's not givin me the error, thank you Norie and romperstomper for this change. Now it gets my

Label11.Caption = "Previous Contract was " & Range("A" & ActiveCell.row) & "/" & Range("M" & ActiveCell.row).text & "/HS" but adding the format as 1 and not 001.

Will try and figure this one out

[Norie]

You are using Text in that line of code.

If you want the textbox to display with leading zeroes try this.

Please Login or Register  to view this content.

[Excelnoub]

Yup was going to post the same thing

Thank you guys