User form code question

[WembleyBear]

Hi

I have a small user form to allow editing of my spreadsheet, using code I
modified from Contextures website. However, there are occasions when the user
might leave one or two of the fields on the user form blank and on this
occasion I would NOT want the corresponding fields on the spreadsheet to be
cleared.

What changes to the code do I need so that the cell is updated if the field
on the user form contains a value but not updated if that particular field is
left blank?

Private Sub CmdUpdate_Click()

Dim ws As Worksheet
Set ws = Worksheets("Rates")

ws.Cells(3, 2).Value = Me.txtRetFran.Value
ws.Cells(3, 3).Value = Me.txtRetNon.Value
ws.Cells(4, 2).Value = Me.txtWarFran.Value
ws.Cells(4, 3).Value = Me.txtWarNon.Value
ws.Cells(5, 2).Value = Me.txtIntFran.Value
ws.Cells(5, 3).Value = Me.txtIntNon.Value


End Sub


Thanks very much
Martyn

Excel 2000, Windows 2003 server over Citrix PS4

 

[Rick Rothstein \(MVP - VB\)]

Let's take the txtRetFran text box as an example. Right now you have this...

ws.Cells(3, 2).Value = Me.txtRetFran.Value

replace the statement with these...

If Len(Trim(Me.txtRetFran.Value)) > 0 Then
ws.Cells(3, 2).Value = Me.txtRetFran.Value
End If

You would make similar changes for each of your other statements. By the
way, the use of the Trim function protects against there being only blank
space characters in the text box... I figured you would not want to
overwrite the current cell contents with blank spaces.

Rick

 

[Bob Phillips]

Private Sub CmdUpdate_Click()

Dim ws As Worksheet
Set ws = Worksheets("Rates")

If Me.txtRetFran.Value <> "" Then ws.Cells(3, 2).Value = Me.txtRetFran.Value
If Me.txtRetNon.Value <> "" Then ws.Cells(3, 3).Value = Me.txtRetNon.Value
If Me.txtWarFran.Value <> "" Then ws.Cells(4, 2).Value = Me.txtWarFran.Value
If Me.txtWarNon.Value <> "" Then ws.Cells(4, 3).Value = Me.txtWarNon.Value
If Me.txtIntFran.Value <> "" Then ws.Cells(5, 2).Value = Me.txtIntFran.Value
If Me.txtIntNon.Value <> "" Then ws.Cells(5, 3).Value = Me.txtIntNon.Value


End Sub

 

[Mike H]

Maybe

Private Sub CmdUpdate_Click()
Dim ws As Worksheet
Set ws = Worksheets("Rates")
For Each ccont In Me.Controls
If TypeName(ccont) = "TextBox" Then
If ccont.Value = "" Then
MsgBox "Fill a value in all textboxes"
Unload Me
Exit Sub
End If
End If
Next ccont
ws.Cells(3, 2).Value = Me.Txtretfran.Value
ws.Cells(3, 3).Value = Me.Txtretnon.Value
ws.Cells(4, 2).Value = Me.Txtwarfran.Value
ws.Cells(4, 3).Value = Me.Txtwarnon.Value
ws.Cells(5, 2).Value = Me.Txtintfran.Value
ws.Cells(5, 3).Value = Me.Txtintnon.Value
End Sub

Mike

 

[WembleyBear]

Hi Bob

Thanks, that did the trick.


Martyn

Private Sub CmdUpdate_Click()

Dim ws As Worksheet
Set ws = Worksheets("Rates")

If Me.txtRetFran.Value <> "" Then ws.Cells(3, 2).Value = Me.txtRetFran.Value
If Me.txtRetNon.Value <> "" Then ws.Cells(3, 3).Value = Me.txtRetNon.Value
If Me.txtWarFran.Value <> "" Then ws.Cells(4, 2).Value = Me.txtWarFran.Value
If Me.txtWarNon.Value <> "" Then ws.Cells(4, 3).Value = Me.txtWarNon.Value
If Me.txtIntFran.Value <> "" Then ws.Cells(5, 2).Value = Me.txtIntFran.Value
If Me.txtIntNon.Value <> "" Then ws.Cells(5, 3).Value = Me.txtIntNon.Value


End Sub


--
__________________________________
HTH

Bob

 

[WembleyBear]

Hi Rick

I tried this as well as Bob's suggestion and it works well too - I can see
why you might want to use the Trim function here.

Thanks
Martyn