VBA Autorefresh format change

G

Guest

Hi,
I have a vba lines to set the right format depending the case value
Everything work fine. However I must click in a cell to make the change.
What I do is:
I select from a dropdown menu the option and this automaticly change the
value and set the format for the numbers. The number change but not the
format until I click on the sheet. Do you have a way to refresh without
clicking?

For your info.
This code is in the sheet (Right click, code,etc)
Thank you everyone.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As String
Select Case Range("D15").Text
Case "$"
C = "$#,##0"
Case "%"
C = "0.0%"
Case "#"
C = "#,##0"
Case "$C"
C = "$ #,##0.00"
Case "#C"
C = "##.0"
Case "T"
C = "###.##"
Case Else
C = "General"
End Select
ActiveSheet.Unprotec
Range("D17:D35,G17:G35,J17:J35,M17:M35,P17:p35,S17:S35,V17:V35,Y17:Y35,AB17:AB35,AE17:AE35,AH17:AH35,AK17:AK35,AN17:AN35").NumberFormat = C
Range("A11").Value = "Click Here"
Range("A10").Select
ActiveSheet.Protect
End Sub
 
S

stjori

Hi,
I have a vba lines to set the right format depending the case value
Everything work fine. However I must click in a cell to make the change.
What I do is:
I select from a dropdown menu the option and this automaticly change the
value and set the format for the numbers. The number change but not the
format until I click on the sheet. Do you have a way to refresh without
clicking?

For your info.
This code is in the sheet (Right click, code,etc)
Thank you everyone.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim C As String
Select Case Range("D15").Text
Case "$"
C = "$#,##0"
Case "%"
C = "0.0%"
Case "#"
C = "#,##0"
Case "$C"
C = "$ #,##0.00"
Case "#C"
C = "##.0"
Case "T"
C = "###.##"
Case Else
C = "General"
End Select
ActiveSheet.Unprotect
Range("D17:D35,G17:G35,J17:J35,M17:M35,P17:p35,S17:S35,V17:V35,Y17:Y35,AB17:AB35,AE17:AE35,AH17:AH35,AK17:AK35,AN17:AN35").NumberFormat = C
Range("A11").Value = "Click Here"
Range("A10").Select
ActiveSheet.Protect
End Sub
Click to expand...

Perhaps try the Worksheet_Change event rather than
Worksheet_SelectionChange?
 
D

Dave Peterson

If you're changing D15 manually, you could use the Worksheet_Change event.

If D15 changes because of a formula, you could use the worksheet_calculate
event.

Here's a sample of the worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As String

'only one cell at a time
If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("D15")) Is Nothing Then Exit Sub

C = ""
Select Case UCase(Range("D15").Text)
Case "$"
C = "$#,##0"
Case "%"
C = "0.0%"
Case "#"
C = "#,##0"
Case "$C"
C = "$ #,##0.00"
Case "#C"
C = "##.0"
Case "T"
C = "###.##"
Case Else
C = "General"
End Select

If C = "" Then
'do nothing
Else
Me.Unprotect
Me.Range("D17:D35,G17:G35,J17:J35," _
& "M17:M35,P17:p35,S17:S35," _
& "V17:V35,Y17:Y35,AB17:AB35," _
& "AE17:AE35,AH17:AH35,AK17:AK35," _
& "AN17:AN35").NumberFormat = C
Me.Protect
End If
End Sub
 
G

Guest

Hi Dave,
Yes a formula change the cell D15.
I tried to use the "worksheet_calculate", now when I do a change,
calculation do not stop.
Maybe I write it in a wrong way.
Can you explain what I do wrong? Thank you
Here what I did:
I just change the first line:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For:
Private Sub Worksheet_calculate()
================= full lines below
Private Sub Worksheet_calculate()
Dim C As String
Select Case Range("D15").Text
Case "$"
C = "$#,##0"
Case "%"
C = "0.0%"
Case "#"
C = "#,##0"
Case "$C"
C = "$ #,##0.00"
Case "#C"
C = "##.0"
Case "T"
C = "###.##"
Case Else
C = "General"
End Select
ActiveSheet.Unprotect
Range("D17:D35,G17:G35,J17:J35,M17:M35,P17:p35,S17:S35,V17:V35,Y17:Y35,AB17:AB35,AE17:AE35,AH17:AH35,AK17:AK35,AN17:AN35").NumberFormat = C
Range("A11").Value = "Click Here"
Range("A10").Select
ActiveSheet.Protect
End Sub
=================
 
D

Dave Peterson

How about:

Option Explicit
Private Sub Worksheet_Calculate()
Dim C As String

C = ""
Select Case UCase(Me.Range("D15").Text)
Case "$"
C = "$#,##0"
Case "%"
C = "0.0%"
Case "#"
C = "#,##0"
Case "$C"
C = "$ #,##0.00"
Case "#C"
C = "##.0"
Case "T"
C = "###.##"
Case Else
C = "General"
End Select

If C = "" Then
'do nothing
Else
Me.Unprotect
Me.Range("D17:D35,G17:G35,J17:J35," _
& "M17:M35,P17:p35,S17:S35," _
& "V17:V35,Y17:Y35,AB17:AB35," _
& "AE17:AE35,AH17:AH35,AK17:AK35," _
& "AN17:AN35").NumberFormat = C
Me.Protect
End If
End Sub
 
G

Guest

THANK YOU
Its working very well.
Small question for my Knowlage. Why " Option Explicit"
When I copy the code a line separate this line
Thank you again
 
D

Dave Peterson

The "option Explicit" is a directive to excel that tells it that you want to be
forced to declare all your variables in that module.

I saved this from a previous post:

If I add "Option Explicit" to the top of a module (or have the VBE do it for me
via tools|options|Editor tab|check require variable declaration), I know that
most of my typos will stop my code from compiling.

Then I don't have to spend minutes/hours looking at code like this:
ctr1 = ctrl + 1
(One is ctr-one and one is ctr-ell)
trying to find why my counter isn't incrementing.

And if I declare my variables nicely:

Dim wks as worksheet
not
dim wks as object
and not
dim wks as variant

I get to use the VBE's intellisense.

If I use "dim wks as worksheet", then I can type:
wks.
(including the dot)
and the VBE will pop up a list of all the properties and methods that I can
use. It saves time coding (for me anyway).

And one final selfish reason.

If I use a variable like:

Dim ThisIsACounterOfValidResponses as Long

I can type
Thisis
and hit ctrl-space and the VBE will either complete the variable name or give me
a list of things that start with those characters.

And by using a combination of upper and lower case letters in my variables, the
VBE will match the case found in the declaration statement.
THANK YOU
Its working very well.
Small question for my Knowlage. Why " Option Explicit"
When I copy the code a line separate this line
Thank you again
Click to expand...
 
G

Guest

Man, your fast with the answer.
Thank you

Now I found another problem with this.
If I go to another sheet in the same workbook and change a number or do
something,
the code I copy in the sheet start working.
Before this, only the sheet I save the code was doing the format change.
Other sheets have others setup and codes
How can I make the code working only on some sheets? (3 sheets).
Also I have an Auto_Open macro to go on a menu. Now When the code start at
the same time on the main menu.

Thank you
 
D

Dave Peterson

If you have formulas on that sheet that get reevaluated when you change
something on a different sheet (say =vlookup() or even a simple =sheet2!a1
formula), then that event will fire.

Maybe you can do something to check to see if the old value in D15 changed. If
it didn't change, then get out. Otherwise, keep going:

Option Explicit
Private Sub Worksheet_Calculate()
Dim C As String
Static OldVal As Variant

If OldVal = Me.Range("D15").Text Then Exit Sub

OldVal = Me.Range("D15").Text

C = ""
Select Case UCase(Me.Range("D15").Text)
Case "$"
C = "$#,##0"
Case "%"
C = "0.0%"
Case "#"
C = "#,##0"
Case "$C"
C = "$ #,##0.00"
Case "#C"
C = "##.0"
Case "T"
C = "###.##"
Case Else
C = "General"
End Select

If C = "" Then
'do nothing
Else
Me.Unprotect
Me.Range("D17:D35,G17:G35,J17:J35," _
& "M17:M35,P17:p35,S17:S35," _
& "V17:V35,Y17:Y35,AB17:AB35," _
& "AE17:AE35,AH17:AH35,AK17:AK35," _
& "AN17:AN35").NumberFormat = C
Me.Protect
End If
End Sub


Man, your fast with the answer.
Thank you

Now I found another problem with this.
If I go to another sheet in the same workbook and change a number or do
something,
the code I copy in the sheet start working.
Before this, only the sheet I save the code was doing the format change.
Other sheets have others setup and codes
How can I make the code working only on some sheets? (3 sheets).
Also I have an Auto_Open macro to go on a menu. Now When the code start at
the same time on the main menu.

Thank you
Click to expand...
 
G

Guest

Hi Dave,
Thank you. Work perfectly. This will help me for others worksheets.
Another small question. You write "ME.Range" instead of a simple " Range "
Why?
Does "ME" replace active sheet. I dont see anythink in the Help about "ME"?

Thank you again
 
D

Dave Peterson

Go into the code and put your cursor on Me.
Hit F1 and you'll see VBA's help.

Essentially, it just means that thing/object that owns the code.

If it's in a worksheet module, it means the worksheet that owns the code.
If it's in the ThisWorkbook module, it means that workbook.
If it's in a userform module, then it means that userform.

Activesheet and Me can be different things.

I once tried to help someone who had a worksheet event that would activate a
bunch of other worksheets. And the easiest (not the best) way to fix things was
to make sure each range was qualified--either with Me or with Activesheet.

me.range("a1")...
or
activesheet.range("a1")...

I think it makes reading the code much easier if you qualify every range.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA, Yahoo ichart csv file 5

Top