Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a cell in sheet to macro or command button
Is there a way to pass a cell in a sheet to a macro or command button?
I have a spreadsheet that I created for a group of users which ultimately creates a csv file to be uploaded to another system. The users input various information and then the spreadsheet performs calculations. I do not want to create the final csv file unless certain conditions are met within the calculations. I have code to create the final csv file (from a command button); however, I can't figure out how to limit the creation of the file based on the parameters from the spreadsheet. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a cell in sheet to macro or command button
If you wish to prevent a commandbutton from being active, you can use
CommandButton1.Enabled = False So in your sheet, you might add use a change event or the calculate event to test if the cell has a specific value which will enable the command button. Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1") = "123" Then CommandButton1.Enabled = True Else CommandButton1.Enabled = False End If End Sub Alternatively include the test in the macro that is initiated by the user pressing the command button, this of course may not do anything and could confuse the user.... 'your sub If Range("A1") < "123" then Exit Sub ' your code -- Cheers Nigel "Connie" wrote in message ups.com... Is there a way to pass a cell in a sheet to a macro or command button? I have a spreadsheet that I created for a group of users which ultimately creates a csv file to be uploaded to another system. The users input various information and then the spreadsheet performs calculations. I do not want to create the final csv file unless certain conditions are met within the calculations. I have code to create the final csv file (from a command button); however, I can't figure out how to limit the creation of the file based on the parameters from the spreadsheet. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass a cell in sheet to macro or command button
Nigel: Both options worked beautifully. I prefer the second option as
this way I can display a message box to let the user know why they can't proceed. Here's the code I used: Private Sub Test_Errors_Click() If Range("TestErrors") = False Then MsgBox "You have unresolved ERRORS. Please View the Error Report and resolve all errors before proceeding." Exit Sub Else My Code End If End Sub Thanks so much for your help! I really appreciate it. Nigel wrote: If you wish to prevent a commandbutton from being active, you can use CommandButton1.Enabled = False So in your sheet, you might add use a change event or the calculate event to test if the cell has a specific value which will enable the command button. Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1") = "123" Then CommandButton1.Enabled = True Else CommandButton1.Enabled = False End If End Sub Alternatively include the test in the macro that is initiated by the user pressing the command button, this of course may not do anything and could confuse the user.... 'your sub If Range("A1") < "123" then Exit Sub ' your code -- Cheers Nigel "Connie" wrote in message ups.com... Is there a way to pass a cell in a sheet to a macro or command button? I have a spreadsheet that I created for a group of users which ultimately creates a csv file to be uploaded to another system. The users input various information and then the spreadsheet performs calculations. I do not want to create the final csv file unless certain conditions are met within the calculations. I have code to create the final csv file (from a command button); however, I can't figure out how to limit the creation of the file based on the parameters from the spreadsheet. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use macro(command button) paste special in 2nd sheet | Excel Worksheet Functions | |||
Calling command button from another sheet | Excel Programming | |||
Command Button Macro SheetNew Workbook | Excel Programming | |||
How do I create a command button to jump from sheet to sheet in a. | Excel Worksheet Functions | |||
Running a Command Button from a different sheet | Excel Programming |