Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
How do I do this?
Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
HI MArk
Im uncetain about your choice of event, might the sub descriptor would refer to the test box such as a texbox change event ? Anyways Ithink incontect you have missed a "s" off of worksheets inyour code ie not worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value but worksheets("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
Mark,
What is it you want to happen, and when? Doug "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
Mark,
Why would you want to load two worksheet cells from a userform EVERY time that you select a cell? And are you sure the form is still in memory at that point? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
Doug, (Bob)
Sorry for the lack of info in the original post, it was late and I was trying to keep it short and sweet ! Anyway what I'm trying to do is use the values in 2 text boxes on the userform as part of 2 time variants in some code which auto saves a copy of a workbook at an interval specified by the user and then stops after a peroid aslo spaecified by the user. Currently these values are enter in 2 cells on the worksheet. The working code is :- Public RunWhen As Double Public RunFor As Double Public cSavePeriodMins As Double Public cRunIntervalSeconds As Double Public Const cRunWhat = "The_SubSave" Public Const cHowLong = "The_SubPeriod" Sub StartTimer() cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=True End Sub Sub StartPeriod() cSavePeriodMins = Worksheets("DDE Sheet").Range("K2").Value RunFor = Now + TimeSerial(0, cSavePeriodMins, 0) Application.OnTime earliesttime:=RunFor, _ procedu=cHowLong, schedule:=True End Sub I've tried :- cRunIntervalSeconds = UserForms("frnOptions").Range("TextBox1").Value cSavePeriodMins = UserForms("frnOptions").Range("TextBox2").Value But this returns a type mismatch Now I've looked at this again and at a reasonable hour I think this may be due to the format of the text box needing to be a number and is by default formated as text but at present I don't know how to change this. Mark "Doug Glancy" wrote: Mark, What is it you want to happen, and when? Doug "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
Mark,
I admit I'm still confused about what you are doing. But, assuming the name of your form is "frnOptions" (with an "n"?) try: cRunIntervalSeconds = frnOptions.TextBox1.Value In your statement below: cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value I assume you really meant "Worksheets" - not "UserForms"? hth, Doug "Mark Dullingham" wrote in message ... Doug, (Bob) Sorry for the lack of info in the original post, it was late and I was trying to keep it short and sweet ! Anyway what I'm trying to do is use the values in 2 text boxes on the userform as part of 2 time variants in some code which auto saves a copy of a workbook at an interval specified by the user and then stops after a peroid aslo spaecified by the user. Currently these values are enter in 2 cells on the worksheet. The working code is :- Public RunWhen As Double Public RunFor As Double Public cSavePeriodMins As Double Public cRunIntervalSeconds As Double Public Const cRunWhat = "The_SubSave" Public Const cHowLong = "The_SubPeriod" Sub StartTimer() cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=True End Sub Sub StartPeriod() cSavePeriodMins = Worksheets("DDE Sheet").Range("K2").Value RunFor = Now + TimeSerial(0, cSavePeriodMins, 0) Application.OnTime earliesttime:=RunFor, _ procedu=cHowLong, schedule:=True End Sub I've tried :- cRunIntervalSeconds = UserForms("frnOptions").Range("TextBox1").Value cSavePeriodMins = UserForms("frnOptions").Range("TextBox2").Value But this returns a type mismatch Now I've looked at this again and at a reasonable hour I think this may be due to the format of the text box needing to be a number and is by default formated as text but at present I don't know how to change this. Mark "Doug Glancy" wrote: Mark, What is it you want to happen, and when? Doug "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
Thanks Doug you solution work perfectly.
Appologies again for the confusion, your assumptions were correct, the now obvious continuity errors in the code I posted was as a result of me trying different things as I was posting my response and not resetting the code properly before I cut and pasted it into the posting. Mark "Doug Glancy" wrote: Mark, I admit I'm still confused about what you are doing. But, assuming the name of your form is "frnOptions" (with an "n"?) try: cRunIntervalSeconds = frnOptions.TextBox1.Value In your statement below: cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value I assume you really meant "Worksheets" - not "UserForms"? hth, Doug "Mark Dullingham" wrote in message ... Doug, (Bob) Sorry for the lack of info in the original post, it was late and I was trying to keep it short and sweet ! Anyway what I'm trying to do is use the values in 2 text boxes on the userform as part of 2 time variants in some code which auto saves a copy of a workbook at an interval specified by the user and then stops after a peroid aslo spaecified by the user. Currently these values are enter in 2 cells on the worksheet. The working code is :- Public RunWhen As Double Public RunFor As Double Public cSavePeriodMins As Double Public cRunIntervalSeconds As Double Public Const cRunWhat = "The_SubSave" Public Const cHowLong = "The_SubPeriod" Sub StartTimer() cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=True End Sub Sub StartPeriod() cSavePeriodMins = Worksheets("DDE Sheet").Range("K2").Value RunFor = Now + TimeSerial(0, cSavePeriodMins, 0) Application.OnTime earliesttime:=RunFor, _ procedu=cHowLong, schedule:=True End Sub I've tried :- cRunIntervalSeconds = UserForms("frnOptions").Range("TextBox1").Value cSavePeriodMins = UserForms("frnOptions").Range("TextBox2").Value But this returns a type mismatch Now I've looked at this again and at a reasonable hour I think this may be due to the format of the text box needing to be a number and is by default formated as text but at present I don't know how to change this. Mark "Doug Glancy" wrote: Mark, What is it you want to happen, and when? Doug "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms Values to Workbook Cells
Mark,
I know how that goes! Glad I could help. Doug "Mark Dullingham" wrote in message ... Thanks Doug you solution work perfectly. Appologies again for the confusion, your assumptions were correct, the now obvious continuity errors in the code I posted was as a result of me trying different things as I was posting my response and not resetting the code properly before I cut and pasted it into the posting. Mark "Doug Glancy" wrote: Mark, I admit I'm still confused about what you are doing. But, assuming the name of your form is "frnOptions" (with an "n"?) try: cRunIntervalSeconds = frnOptions.TextBox1.Value In your statement below: cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value I assume you really meant "Worksheets" - not "UserForms"? hth, Doug "Mark Dullingham" wrote in message ... Doug, (Bob) Sorry for the lack of info in the original post, it was late and I was trying to keep it short and sweet ! Anyway what I'm trying to do is use the values in 2 text boxes on the userform as part of 2 time variants in some code which auto saves a copy of a workbook at an interval specified by the user and then stops after a peroid aslo spaecified by the user. Currently these values are enter in 2 cells on the worksheet. The working code is :- Public RunWhen As Double Public RunFor As Double Public cSavePeriodMins As Double Public cRunIntervalSeconds As Double Public Const cRunWhat = "The_SubSave" Public Const cHowLong = "The_SubPeriod" Sub StartTimer() cRunIntervalSeconds = UserForms("DDE Sheet").Range("I2").Value RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds) Application.OnTime earliesttime:=RunWhen, _ procedu=cRunWhat, schedule:=True End Sub Sub StartPeriod() cSavePeriodMins = Worksheets("DDE Sheet").Range("K2").Value RunFor = Now + TimeSerial(0, cSavePeriodMins, 0) Application.OnTime earliesttime:=RunFor, _ procedu=cHowLong, schedule:=True End Sub I've tried :- cRunIntervalSeconds = UserForms("frnOptions").Range("TextBox1").Value cSavePeriodMins = UserForms("frnOptions").Range("TextBox2").Value But this returns a type mismatch Now I've looked at this again and at a reasonable hour I think this may be due to the format of the text box needing to be a number and is by default formated as text but at present I don't know how to change this. Mark "Doug Glancy" wrote: Mark, What is it you want to happen, and when? Doug "Mark Dullingham" wrote in message ... How do I do this? Ive tried this code in the worksheet and the userform bu is doesn't work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheet("DDE Sheet").Range("I2").Value = frmOptions.TextBox1.Value Worksheet("DDE Sheet").Range("K2").Value = frmOptions.TextBox2.Value End Sub Am I just being thick! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return values from UserForms | Excel Programming | |||
Shared Workbook UserForms | Excel Discussion (Misc queries) | |||
Passing variable values from userforms | Excel Programming | |||
UserForms and workbook manipulation | Excel Programming | |||
UserForms to deactivate workbook | Excel Programming |