Setting Default worksheet column value 2

[jmstarbuck]

I am exporting data to an excel worksheet. I would like to set default values for some of my columns, so that the user doesn't have to worry about those fields when he inserts a row.

Here's what I am doing now.

 With .Columns("A:A")
            With .Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                   xlBetween, Formula1:=ProgramYearCmb
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                '.ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = "The value must be " & ProgramYearCmb & "."
                .ShowInput = True
                .ShowError = True
            End With
        End With

I found a reference to listdataformat.defaultvalue somewhere, but I can't figure out how it would fit in.

Is there a way to set a default value?

J

 

[randy700]

How would you do it if you were working directly with Excel?

Randy

 

[jmstarbuck]

I don't know. I tried to figure that out so that I could just record a macro to see the code. I must be missing something. This seems fundamental. Do you know how to do it in Excel?

J

 

[PHV]

I found a reference to listdataformat.defaultvalue
This is meaningfull in a sharepoint environment only.

http://msdn.microsoft.com/en-us/library/aa214278(v=office.11).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

I know of no way to set default values on indivdual cells in Excel when creating a new column.
My point being, if you can't do it in Excel, you probably can't do it from Access either.


Randy

 

[Gruuuu]

Learned this one from Skip...
Create a list!

Workbooks("yerBook.xls").Sheets("yerSheet").ListObjects.Add (xlSourceRange, Range("A1:C2"), , xlYes).Name ="List1"
Range("A2").Formula = "=" & ProgramYearCmb
Range("C2").Formula = "=TODAY()" [green]'for example[/green]
[green]'Start filling in your data. Skip over the columns with formulas. The list will automatically expand as you fill the rows, and any column with formulas will fill new cells with the formula[/green]

 

[jmstarbuck]

Thanks, Gruuuu!

I just knew it had to be possible. Where there is code, there is a way!

J

 

[SkipVought]

Go Gruuuu! ==> *

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Thank you sirs!