Simple userform help

[auschten]

I'm pretty new to Excel, but I have used userforms in
Word before; I think I just need some help getting
started. What I want is to have a userform that will
increase prices in a worksheet by x%. The user will type
in the number on the userform and click submit. Then all
the prices already in columns B and C will increase by
that percent. I know that sounds ridiculously simple, but
I can't even think of how to get the values from the
worksheet and put new values back in.

Should I even bother with a userform? I suppose I could
create a separate worksheet for them to enter in the %
increase, but what if they add or remove items?

Thanks in advance!
Auschten

 

[Bob Phillips]

AUschten,

A form does seem overkill, why not just capture cell input.

Anyway, assuming the percentage goes in a textbox, this code will do it

Dim cell As Range

For Each cell In ActiveSheet.Range("B:C")
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
cell.Value = cell.Value * (1 + TextBox1.Text)
End If
End If
Next cell

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim dblMult As Double
Dim cell As Range, rngB As Range
Dim rngC As Range
If IsNumeric(TextBox1.Value) And _
Len(TextBox1.Value) <> 0 Then
dblMult = CDbl(TextBox1.Value)
If dblMult <> 0 Then
With Worksheets("Sheet1")
Set rngB = .Range(.Cells(1, "B"), _
.Cells(Rows.Count, "B").End(xlUp))
Set rngC = .Range(.Cells(1, "C"), _
.Cells(Rows.Count, "C").End(xlUp))
End With
For Each cell In rngB
if isnumeric(cell) then
cell.Value = cell.Value * (1 + dblMult)
End if
Next
For Each cell In rngC
if isnumeric(cell) then
cell.Value = cell.Value * (1 + dblMult)
end if
Next
End If
End If ' isnumeric
End Sub

Greater knowledge of your data could significantly reduce the code needed.

 

[Tom Ogilvy]

For Each cell In ActiveSheet.Range("B:C")

That's kind of cruel, isn't it <g>

 

[Guest]

Thanks so much, both of you. I think this will be a big
help!

Auschten

 

[Bob Phillips]

It actually worked very fast on my machine - not expected<vbg>

Bob