sheet code question and cmd button question

A1058

New Member
Joined
Sep 13, 2006
Messages
46
guys hoping someone can help out...
ive got some sheet code that changes the interior colors of some cells. which works just fine but doesnt run until the user enters any value into the sheet. how can i make it work witout having to have entered anything. maybe when the sheet becomes active??? also one more question i have a command button that executes a list of things when clicked how can i make it ask the user "Are you sure" yes or no ???

any help is appreciated
thank you
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Can you post your code?

For the second part you can use something like:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> DoMe()
    <SPAN style="color:#00007F">Dim</SPAN> Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    Response = MsgBox("Do you want to proceed?", vbQuestion + vbYesNoCancel, "Proceed?")
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Response
        <SPAN style="color:#00007F">Case</SPAN> vbYes
            MsgBox "Proceeding"
        <SPAN style="color:#00007F">Case</SPAN> vbNo
            MsgBox "Exiting"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty
 

A1058

New Member
Joined
Sep 13, 2006
Messages
46
thank you smitty i had just figure out the yes/no msgbox. mine wasnt as pretty looking as yours but functional. ill use yours i like the additional proceeding message.. ill post ya a small section of the code in each sheet that doesnt do anything until the user enters some value in the sheet.

Code:
'======================
Dim Sh As Worksheet

With Sh
  If [a42].Value <= 0 Then
      [h12:h14].Interior.ColorIndex = 0
    ElseIf [a42].Value > 0 And [a42].Value <= 1 Then
      [h12].Interior.ColorIndex = 6
    ElseIf [a42].Value > 1 And [a42].Value <= 2 Then
      [h12:h13].Interior.ColorIndex = 6
    ElseIf [a42].Value > 2 And [a42].Value <= 3 Then
      [h12:h14].Interior.ColorIndex = 6
    Else
  End If
End With
'========================

would be nice if this would happen as soon as the sheet is activated(there are 15 sheets in my workbook they are all identical..

EDIT: Added Code tags - Smitty
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
would be nice if this would happen as soon as the sheet is activated

There is a global sheet activate event:

Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Just put your code there and it'll run on every sheet.

Smitty
 

A1058

New Member
Joined
Sep 13, 2006
Messages
46
awesome smitty. thanks a million.. im going to make those changes right now see how it comes out..
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,710
Members
410,631
Latest member
JFORTH97
Top