Replace all values in column with combobox selection

[RatherBeRidi]

I have a userform with a single combobox (cboReportPeriod) and command button (cmdAdd). The combobox gets its values from a range titled ReportPeriodList on the worksheet titled lookUpList. The range is month and year as text (e.g., January 2012, February 2012). I have another range titled CurrentReportPeriod (=Sheet1!$A$2:$A$1048576) on sheet 1.

I want all cells that have entries in column A to be replaced by the selection made in the combobox. I can open the form, make a selection from the combobox, and clicking the command button appears to be doing "something", but the existing text in column A is still the same. Do I have the wrong syntax for If cell.Value = True?

Private Sub cmdAdd_Click()
Dim cbo As ComboBox
Dim cell As Range
For Each cell In Range("CurrentReportPeriod")
    If cell.Value = True Then
        cell.Value = Me.cboReportPeriod.Value
    End If
Next cell
Me.cboReportPeriod.Value = ""
Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim i As Long
Dim ReportPeriodList As Range
Dim cbo As ComboBox
Dim ws As Worksheet
Set ws = Worksheets("lookUpList")
For i = 1 To Range("ReportPeriodList").Rows.Count
    Adjusted the code tagsMe.cboReportPeriod.AddItem Range("ReportPeriodList")(i)
Next i
End Sub

[Bob Phillips]

Private Sub cmdAdd_Click()
Range("CurrentReportPeriod").Replace What:="*", _
Replacement:=Me.cboReportPeriod.Value, _
LookAt:=xlWhole, SearchOrder:=xlByRows
Me.cboReportPeriod.Value = ""
Unload Me
End Sub

[RatherBeRidi]

Super! It's almost perfect, though I don't understand it. But, even though I formatted the column as text, it inserts a date. For example, the lookUpList is February 2012, the cmdAdd inserts it as Feb-12. Anyway to format the text? Thank you.

[Bob Phillips]

Does this do it

Private Sub cmdAdd_Click() 
    With Range("CurrentReportPeriod")
    .Replace What:="*", _ 
    Replacement:=Me.cboReportPeriod.Value, _ 
    LookAt:=xlWhole, _ 
    SearchOrder:=xlByRows 
    .NumberFormat = "dddd-yyyy"
    End With
Me.cboReportPeriod.Value = "" 
    Unload Me 
End Sub

[RatherBeRidi]

Thanks, but not quite. It still stores the value as a date. I changed part of your code to .NumberFormat = "mmmm yyyy". The cell value displays as February 2012, but 4/1/2012 is in the formula bar. I'd like the value to be stored as text, not a date. Any ideas?

[Bob Phillips]

I can't understand why you would do that, but ...

Private Sub cmdAdd_Click()
    With Range("CurrentReportPeriod")
    .Replace What:="*", _
    Replacement:="'" & Format(Me.cboReportPeriod.Value, "mmmm yyyy"), _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows
End With
Me.cboReportPeriod.Value = ""
    Unload Me
End Sub

[RatherBeRidi]

Thanks much for being so quick with your response. Sorry for the slowness of my response. I got side tracked with other problems with this project. I wanted a reporting period of month and year - not a specific date. Your most recent suggestion does produce the month and year preceded by the apostrophe. I can work with either for analysis.

But, I have run into another snag. Using your post #4, when I protect the sheet I get an error message: Run-time error 1004 Unable to set the NumberFormat property of the Range class. Both of your examples work if the sheet is not protected. I really need to protect the sheet to avoid changes to formulas. I expect it is possible to unprotect a sheet programmatically. I've tried to unlock the cells in code, but still no success. Can you help me out with the code for protect/unprotect and/or lock/unlock? I am using Excel 2010 if it makes a difference. Thank you.

[Bob Phillips]

No worries, we are in no hurry.

In this case, our code can handle the protection. I assume here that you are not using a password

Private Sub cmdAdd_Click() 
    Activesheet.Unprotect
With Range("CurrentReportPeriod") 
    .Replace What:="*", _ 
    Replacement:="'" & Format(Me.cboReportPeriod.Value, "mmmm yyyy"), _ 
    LookAt:=xlWhole, _ 
    SearchOrder:=xlByRows 
    End With 
Activesheet.Protect
Me.cboReportPeriod.Value = "" 
    Unload Me 
End Sub