I have an input userform that has OK, Cancel and Clear buttons. when
you input something and click OK, it writes it to a spreadsheet. If
you click Cancel it closes the form an does not input anything. If you
click clear, it clears all the values in the form. it clears it by
clearing the contents of the spreadsheet then reactivating the form
which reads the input from the cells. I tried to write a sub so that
when you clear the input and then click cancel, the input will return
to what it was before clicking on clear (when the form is opened
again). here ar ethe subs for Cancel and Clear:

Private Sub CommandButtonCLear_Click()
Sheets("sheet1").Range("D34:H39").Value =
Sheets("sheet1").Range("B8:F13").Value
Sheets("sheet1").Range("B8:F13").ClearContents

Unload UserFormMultiLC
UserFormMultiLC.Show
Sheets("sheet1").Range("D33").Value = 1
End Sub
----------------------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButtonMultiLCCancel_Click()
If Sheets("sheet1").Range("D33").Value = 1 Then
Sheets("sheet1").Range("B8:F13").Value =
Sheets("sheet1").Range("D34:H39").Value
Else
End If

UserFormMultiLC.Hide
End Sub


the clear sub is working fine. it writes the input range to a new
range before clearing the input range and restarting the userform. but
when I click the cancel button, it is not writing the saved input
range. into the original input range. I cannot understand why. does
this make sense? is there a better way to go about this? thanks.

Jacob -
from what i can see (being a non-guru), it looks to me
like your 2nd range simply EQUALS what the first range
consists of. then when you clear the initial range, you
are inadvertently clearing the 2nd range, too.

to try to illustrate:
YourSub clear()
cell b3 = "5"
cell x3 = "=b3"
when you clear b3, x3 also clears.
(if i'm understanding your code correctly).

why don't you try (in simplified language):
YourSub clear()
cell b3 = "5"
copy b3 to x3
clear b3

now your old values are still contained in x3, for
re-installing when they choose
YourSub cancel().

susan

Private Sub CommandButtonMultiLCCancel_Click()
With Sheets("sheet1")
If .Range("D33").Value = 1 Then
.Range("D34:H39").Copy .Range("B8:F13")
End If
End With
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jacob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have an input userform that has OK, Cancel and Clear buttons. when
> you input something and click OK, it writes it to a spreadsheet. If
> you click Cancel it closes the form an does not input anything. If you
> click clear, it clears all the values in the form. it clears it by
> clearing the contents of the spreadsheet then reactivating the form
> which reads the input from the cells. I tried to write a sub so that
> when you clear the input and then click cancel, the input will return
> to what it was before clicking on clear (when the form is opened
> again). here ar ethe subs for Cancel and Clear:
>
> Private Sub CommandButtonCLear_Click()
> Sheets("sheet1").Range("D34:H39").Value =
> Sheets("sheet1").Range("B8:F13").Value
> Sheets("sheet1").Range("B8:F13").ClearContents
>
> Unload UserFormMultiLC
> UserFormMultiLC.Show
> Sheets("sheet1").Range("D33").Value = 1
> End Sub
> --------------------------------------------------------------------------
--------------------------------------------------------------
> Private Sub CommandButtonMultiLCCancel_Click()
> If Sheets("sheet1").Range("D33").Value = 1 Then
> Sheets("sheet1").Range("B8:F13").Value =
> Sheets("sheet1").Range("D34:H39").Value
> Else
> End If
>
> UserFormMultiLC.Hide
> End Sub
>
>
> the clear sub is working fine. it writes the input range to a new
> range before clearing the input range and restarting the userform. but
> when I click the cancel button, it is not writing the saved input
> range. into the original input range. I cannot understand why. does
> this make sense? is there a better way to go about this? thanks.
>

I understand what you are describing, but I have checked that. when I
click the clear button, everything does what I want it to do. range1
is cleared and range2 is updated with whatever range1 was before it was
cleared. and cell D33 is changed to 1 which indicated the cells have
been cleared.

when I click the cancel button, according to my code, if cell D33 is 1
(which it will be if the cells have been cleared) it will only copy
range2 to range1 and that's it. there is no clearing involved in the
cancel sub. instead, the cells are not being copied to range1 and
range2 does not change. so I know they are not being cleared, or
overwritten with zero values.

ok, so it's the blind leading the blind..........
maybe bob's idea will work.

susan

bob,
that does the exact same thing that my code does. I tried it. I
cannot figure out why Range("B8:F13") will not copy to Range("B8:F13").
it makes no sense. it works fine the other way around. thanks for
your help.


On Oct 19, 11:41 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> Private Sub CommandButtonMultiLCCancel_Click()
> With Sheets("sheet1")
> If .Range("D33").Value = 1 Then
> .Range("B8:F13").Copy .Range("B8:F13")
> End If
> End With
> End If
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jacob" <jlong...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > I have an input userform that has OK, Cancel and Clear buttons. when
> > you input something and click OK, it writes it to a spreadsheet. If
> > you click Cancel it closes the form an does not input anything. If you
> > click clear, it clears all the values in the form. it clears it by
> > clearing the contents of the spreadsheet then reactivating the form
> > which reads the input from the cells. I tried to write a sub so that
> > when you clear the input and then click cancel, the input will return
> > to what it was before clicking on clear (when the form is opened
> > again). here ar ethe subs for Cancel and Clear:
>
> > Private Sub CommandButtonCLear_Click()
> > Sheets("sheet1").Range("D34:H39").Value =
> > Sheets("sheet1").Range("B8:F13").Value
> > Sheets("sheet1").Range("B8:F13").ClearContents
>
> > Unload UserFormMultiLC
> > UserFormMultiLC.Show
> > Sheets("sheet1").Range("D33").Value = 1
> > End Sub
> > ----------------------------------------------------------------------------------------------------------------------------------------
>
> > Private Sub CommandButtonMultiLCCancel_Click()
> > If Sheets("sheet1").Range("D33").Value = 1 Then
> > Sheets("sheet1").Range("B8:F13").Value =
> > Sheets("sheet1").Range("D34:H39").Value
> > Else
> > End If
>
> > UserFormMultiLC.Hide
> > End Sub
>
> > the clear sub is working fine. it writes the input range to a new
> > range before clearing the input range and restarting the userform. but
> > when I click the cancel button, it is not writing the saved input
> > range. into the original input range. I cannot understand why. does
> > this make sense? is there a better way to go about this? thanks.