Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click event not working
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click event not working
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click event not working
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" wrote in message ups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click event not working
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click event not working
ok, so it's the blind leading the blind..........
maybe bob's idea will work. :) susan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click event not working
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" 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" wrote in oglegroups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform label double-click goes to click event | Excel Programming | |||
Select Multi in Listbox - Event Click not working | Excel Programming | |||
Click event to run only once | Excel Programming | |||
Click event to run only once | Excel Programming | |||
Click Event | Excel Programming |