Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Hi,
I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
As a user I prefer to be presented with valid choices only, rather that be
allowed to pick an invalid choice and then be told it wasn't right. If it was wrong why did you give me that choice? (I ask). So, in your situation, I'd use the option buttons' Enabled property to turn off invalid choices before the user even sees the userform. -- Jim Rech Excel MVP "ewan7279" wrote in message ... | Hi, | | I have created macros to update cost centre spreadsheets from a range of | data. The macros run from a form that has 12 options (1 for each month) and | I want to display a warning message to the user to ensure that they have | selected the correct month. | | The macros currently enter the number relating to the month chosen into a | cell on one of the sheets. I have used the validation option to give a | warning for any entry put into the cell, but data entered by macros does not | cause the warning to display. | | Can anyone offer a solution to this problem please? | | Thanks, | | Ewan. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
The macros currently enter the number relating to the month chosen into a
cell on one of the sheets. Can't your macro validate the data? -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi, I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Jim,
There may be a valid reason for wanting to rerun a previous month's data - late corrections etc - so all the options are valid at any time. The macro is only used once per month, but I would like the user to validate their choice before the update takes place. Ewan. "Jim Rech" wrote: As a user I prefer to be presented with valid choices only, rather that be allowed to pick an invalid choice and then be told it wasn't right. If it was wrong why did you give me that choice? (I ask). So, in your situation, I'd use the option buttons' Enabled property to turn off invalid choices before the user even sees the userform. -- Jim Rech Excel MVP "ewan7279" wrote in message ... | Hi, | | I have created macros to update cost centre spreadsheets from a range of | data. The macros run from a form that has 12 options (1 for each month) and | I want to display a warning message to the user to ensure that they have | selected the correct month. | | The macros currently enter the number relating to the month chosen into a | cell on one of the sheets. I have used the validation option to give a | warning for any entry put into the cell, but data entered by macros does not | cause the warning to display. | | Can anyone offer a solution to this problem please? | | Thanks, | | Ewan. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Tom,
That is why I'm on this site - I'm no programmer and can't work out how to validate the user's choice. Any constructive help would be appreciated. Ewan. "Tom Ogilvy" wrote: The macros currently enter the number relating to the month chosen into a cell on one of the sheets. Can't your macro validate the data? -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi, I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Instead of having code like
write value to sheet use code like if value chosen is not in acceptable limits then msgbox value chosen is not in acceptable limits else write value to sheet end if -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Tom, That is why I'm on this site - I'm no programmer and can't work out how to validate the user's choice. Any constructive help would be appreciated. Ewan. "Tom Ogilvy" wrote: The macros currently enter the number relating to the month chosen into a cell on one of the sheets. Can't your macro validate the data? -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi, I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Hi Tom,
I've found a way round using a nested If statement (taught myself today - check me out!! ;-) The user now has to input the number relating to the month they wish to update and click one button on the form, rather than having a different button (and macro) for each month. The macro then looks at the number entered and using a nested If statement (I couldn't work out Case statements - would probably be neater) enters the data into the appropriate month columns in the spreadsheets. Thanks anyway, Ewan. "Tom Ogilvy" wrote: Instead of having code like write value to sheet use code like if value chosen is not in acceptable limits then msgbox value chosen is not in acceptable limits else write value to sheet end if -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Tom, That is why I'm on this site - I'm no programmer and can't work out how to validate the user's choice. Any constructive help would be appreciated. Ewan. "Tom Ogilvy" wrote: The macros currently enter the number relating to the month chosen into a cell on one of the sheets. Can't your macro validate the data? -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi, I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Sounds good.
Just another suggestion, maybe you want to use the Choose function or perhaps you can calculate the column position from the number provided. -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi Tom, I've found a way round using a nested If statement (taught myself today - check me out!! ;-) The user now has to input the number relating to the month they wish to update and click one button on the form, rather than having a different button (and macro) for each month. The macro then looks at the number entered and using a nested If statement (I couldn't work out Case statements - would probably be neater) enters the data into the appropriate month columns in the spreadsheets. Thanks anyway, Ewan. "Tom Ogilvy" wrote: Instead of having code like write value to sheet use code like if value chosen is not in acceptable limits then msgbox value chosen is not in acceptable limits else write value to sheet end if -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Tom, That is why I'm on this site - I'm no programmer and can't work out how to validate the user's choice. Any constructive help would be appreciated. Ewan. "Tom Ogilvy" wrote: The macros currently enter the number relating to the month chosen into a cell on one of the sheets. Can't your macro validate the data? -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi, I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation warning for macro data
Thanks - food for thought. I'm sure I'll manage to reduce the code to its
bear minimum in the next few weeks as I get more experience. "Tom Ogilvy" wrote: Sounds good. Just another suggestion, maybe you want to use the Choose function or perhaps you can calculate the column position from the number provided. -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi Tom, I've found a way round using a nested If statement (taught myself today - check me out!! ;-) The user now has to input the number relating to the month they wish to update and click one button on the form, rather than having a different button (and macro) for each month. The macro then looks at the number entered and using a nested If statement (I couldn't work out Case statements - would probably be neater) enters the data into the appropriate month columns in the spreadsheets. Thanks anyway, Ewan. "Tom Ogilvy" wrote: Instead of having code like write value to sheet use code like if value chosen is not in acceptable limits then msgbox value chosen is not in acceptable limits else write value to sheet end if -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Tom, That is why I'm on this site - I'm no programmer and can't work out how to validate the user's choice. Any constructive help would be appreciated. Ewan. "Tom Ogilvy" wrote: The macros currently enter the number relating to the month chosen into a cell on one of the sheets. Can't your macro validate the data? -- Regards, Tom Ogilvy "ewan7279" wrote in message ... Hi, I have created macros to update cost centre spreadsheets from a range of data. The macros run from a form that has 12 options (1 for each month) and I want to display a warning message to the user to ensure that they have selected the correct month. The macros currently enter the number relating to the month chosen into a cell on one of the sheets. I have used the validation option to give a warning for any entry put into the cell, but data entered by macros does not cause the warning to display. Can anyone offer a solution to this problem please? Thanks, Ewan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List Macro | Excel Discussion (Misc queries) | |||
Macro Data Entry Validation | Excel Worksheet Functions | |||
trigger data validation warning | Excel Discussion (Misc queries) | |||
Data Validation and Macro | Excel Programming | |||
Macro on a data validation list | Excel Programming |