PDA

View Full Version : [SOLVED] Replace all values in column with combobox selection



RatherBeRidi
05-02-2012, 10:29 AM
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
05-02-2012, 10:55 AM
Private Sub cmdAdd_Click()
Range("CurrentReportPeriod").Replace What:="*", _
Replacement:=Me.cboReportPeriod.Value, _
LookAt:=xlWhole, SearchOrder:=xlByRows
Me.cboReportPeriod.Value = ""
Unload Me
End Sub

RatherBeRidi
05-02-2012, 11:19 AM
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
05-02-2012, 12:14 PM
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
05-03-2012, 10:53 AM
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
05-03-2012, 03:01 PM
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
05-24-2012, 08:18 AM
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
05-24-2012, 09:27 AM
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