|
VBA Worksheet Function Question
I am trying to setup my Worksheet to only Enable buttons when there is
a date present in cell B2. The script I have below works, but has some issues. Message box comes up twice, etc.. This is part of a Large Workbook with many macros the users steps thru. I wanted to only enable the button in the proper order. Example: A date must be in cell B2 to enable Button-1. Once Button-1 is pushed Button-2 is enabled and Button-1 is Disabled. any ideas or suggestions would be appreciated. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Msg, Style, Title, Response Sheets("Study Dates").Select Range("B2").Select Selection.NumberFormat = "d-mmm-yy" If IsEmpty(ActiveCell) = True Then Day1Button.Enabled = False Day2Button.Enabled = False Day3Button.Enabled = False Day4Button.Enabled = False Day5Button.Enabled = False Msg = "Please Enter a START Date in cell B2" ' Define message. Style = vbOKOnly + vbDefaultButton1 ' Define buttons. Title = "MsgBox Demonstration" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) Else Day1Button.Enabled = True End If End Sub Private Sub Day1Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day2Button.Enabled = True Day1Button.Enabled = False End Sub Private Sub Day2Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day3Button.Enabled = True Day2Button.Enabled = False End Sub Private Sub Day3Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day4Button.Enabled = True Day3Button.Enabled = False End Sub Private Sub Day4Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day5Button.Enabled = True Day4Button.Enabled = False End Sub Private Sub Day5Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day5Button.Enabled = False Day1Button.Enabled = True End Sub |
VBA Worksheet Function Question
Your selection change macro makes a selection which fires the selection
change macro... Try this... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Msg, Style, Title, Response Application.EnableEvents = False Sheets("Study Dates").Range("B2").NumberFormat = "d-mmm-yy" If IsEmpty(Sheets("Study Dates").Range("B2")) = True Then Day1Button.Enabled = False Day2Button.Enabled = False Day3Button.Enabled = False Day4Button.Enabled = False Day5Button.Enabled = False Msg = "Please Enter a START Date in cell B2" ' Define message. Style = vbOKOnly + vbDefaultButton1 ' Define buttons. Title = "MsgBox Demonstration" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) Else Day1Button.Enabled = True End If Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "VexedFist" wrote: I am trying to setup my Worksheet to only Enable buttons when there is a date present in cell B2. The script I have below works, but has some issues. Message box comes up twice, etc.. This is part of a Large Workbook with many macros the users steps thru. I wanted to only enable the button in the proper order. Example: A date must be in cell B2 to enable Button-1. Once Button-1 is pushed Button-2 is enabled and Button-1 is Disabled. any ideas or suggestions would be appreciated. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Msg, Style, Title, Response Sheets("Study Dates").Select Range("B2").Select Selection.NumberFormat = "d-mmm-yy" If IsEmpty(ActiveCell) = True Then Day1Button.Enabled = False Day2Button.Enabled = False Day3Button.Enabled = False Day4Button.Enabled = False Day5Button.Enabled = False Msg = "Please Enter a START Date in cell B2" ' Define message. Style = vbOKOnly + vbDefaultButton1 ' Define buttons. Title = "MsgBox Demonstration" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) Else Day1Button.Enabled = True End If End Sub Private Sub Day1Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day2Button.Enabled = True Day1Button.Enabled = False End Sub Private Sub Day2Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day3Button.Enabled = True Day2Button.Enabled = False End Sub Private Sub Day3Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day4Button.Enabled = True Day3Button.Enabled = False End Sub Private Sub Day4Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day5Button.Enabled = True Day4Button.Enabled = False End Sub Private Sub Day5Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day5Button.Enabled = False Day1Button.Enabled = True End Sub |
VBA Worksheet Function Question
JIM,
WORKED GREAT Jim Thomlinson wrote: Your selection change macro makes a selection which fires the selection change macro... Try this... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Msg, Style, Title, Response Application.EnableEvents = False Sheets("Study Dates").Range("B2").NumberFormat = "d-mmm-yy" If IsEmpty(Sheets("Study Dates").Range("B2")) = True Then Day1Button.Enabled = False Day2Button.Enabled = False Day3Button.Enabled = False Day4Button.Enabled = False Day5Button.Enabled = False Msg = "Please Enter a START Date in cell B2" ' Define message. Style = vbOKOnly + vbDefaultButton1 ' Define buttons. Title = "MsgBox Demonstration" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) Else Day1Button.Enabled = True End If Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "VexedFist" wrote: I am trying to setup my Worksheet to only Enable buttons when there is a date present in cell B2. The script I have below works, but has some issues. Message box comes up twice, etc.. This is part of a Large Workbook with many macros the users steps thru. I wanted to only enable the button in the proper order. Example: A date must be in cell B2 to enable Button-1. Once Button-1 is pushed Button-2 is enabled and Button-1 is Disabled. any ideas or suggestions would be appreciated. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Msg, Style, Title, Response Sheets("Study Dates").Select Range("B2").Select Selection.NumberFormat = "d-mmm-yy" If IsEmpty(ActiveCell) = True Then Day1Button.Enabled = False Day2Button.Enabled = False Day3Button.Enabled = False Day4Button.Enabled = False Day5Button.Enabled = False Msg = "Please Enter a START Date in cell B2" ' Define message. Style = vbOKOnly + vbDefaultButton1 ' Define buttons. Title = "MsgBox Demonstration" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) Else Day1Button.Enabled = True End If End Sub Private Sub Day1Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day2Button.Enabled = True Day1Button.Enabled = False End Sub Private Sub Day2Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day3Button.Enabled = True Day2Button.Enabled = False End Sub Private Sub Day3Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day4Button.Enabled = True Day3Button.Enabled = False End Sub Private Sub Day4Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day5Button.Enabled = True Day4Button.Enabled = False End Sub Private Sub Day5Button_Click() Application.Run "'MACRO AT&T Traffic Studies.xls'!Day1Macro" Day5Button.Enabled = False Day1Button.Enabled = True End Sub |
| All times are GMT +1. The time now is 10:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com