Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA Worksheet Function Question
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Basic Question - How do I return the worksheet number of the active worksheet? Regnab Excel Programming 2 May 17th 06 03:02 AM
Reference the worksheet from a multiple worksheet range function ( DBickel Excel Worksheet Functions 1 May 28th 05 03:49 AM
Can the offset worksheet function reference another worksheet AlistairJ Excel Worksheet Functions 2 May 9th 05 06:18 PM
formula/function to copy from worksheet to worksheet Jen Excel Programming 5 January 11th 05 08:22 PM
end of worksheet question olmedic Excel Worksheet Functions 1 October 29th 04 08:55 PM


All times are GMT +1. The time now is 04:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement