Re: Dynamic Ranges/Automatically Updating Charts
Found the problem,
Select the y-axis -> format axis -> set the min and the max value axis scale on auto.
Re: Dynamic Ranges/Automatically Updating Charts
Found the problem,
Select the y-axis -> format axis -> set the min and the max value axis scale on auto.
Re: Dynamic Ranges/Automatically Updating Charts
Hi,
I'm trying to understand how the max and the min y-scale value is set.
Can someone explane me how it is done, or how I can do that?
Thanx,
Fluppe
Re: button name
Ok thank you. This helps me alot!
Hi, I use this code as a routine to create buttons on workbook("meetpunten.xls").worksheets("sheet1")
Public Sub ophalen_plaatsen_gegevens()
Application.ScreenUpdating = False
With Workbooks("meetpunten.xls").Worksheets("Sheet1")
'set de buttonname
strButtonname = .Range("a1").Range(varCeladres).Offset(0, 1).Value
'set de buttoncaption
strButtoncaption = .Range("a1").Range(varCeladres).Offset(0, 1).Value
End With
'plaatsen van nieuwe button
ActiveSheet.Buttons.Add(30, intXpositie, 100, 30).Select
'properties buttons
With Selection
.OnAction = "oproep_macro"
.Name = strButtonname
.Caption = strButtoncaption
'button niet mee verschalen bij aanpassen van rijhoogte en/of kolombreedte
.Placement = xlFreeFloating
.PrintObject = True
End With
'tekst properties buttoncaption
With Selection.Font
.Name = "Arial"
.FontStyle = "Vet"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = 11
End With
'x-positie aanpassen
intXpositie = intXpositie + 40
'uitlezen van overeenkomstige gegevens
Call ophalen_en_uitlezen_meetgegevens
Display More
I'm trying to find the syntax that returns the buttonname of the button the user clicked on. This seems easy but I don't know how?
Thanks,
Fluppe
Hi all,
I use the following macro to create a series of commandbuttons:
Option Explicit
Dim strButtonname As String
Dim strButtoncaption As String
Dim intI As Integer
Dim intXpositie As Integer
Public Sub click_cmd1()
Call check_groupname
End Sub
Public Sub check_groupname()
intXpositie = 65 'buttons onder elkaar plaatsen
Workbooks("overzicht.xls").Worksheets("Sheet1").Activate
For intI = 1 To 4
Application.ScreenUpdating = False
'plaatsen avn nieuwe button
ActiveSheet.Buttons.Add(30, intXpositie, 100, 30).Select
'macro aanhangen
Selection.OnAction = "Macro3"
'naam geven aan button
strButtonname = Workbooks("meetpunten.xls").Worksheets("Sheet1").Range("A1").Offset(intI, 0).Value
On Error Resume Next
ActiveSheet.Shapes(strButtonname).Select
Selection.Characters.Text = Workbooks("meetpunten.xls").Worksheets("Sheet1").Range("A1").Offset(intI, 1).Value
strButtoncaption = Selection.Characters.Text
intXpositie = intXpositie + 40
Next intI
End Sub
Display More
I want to have a button name (strButtonname) the same as the Selection.Characters.text. But when I select the new button on my sheet excel named the new buttons as “button 2” “button 3”…
What did I do wrong?
Thanks,
Fluppe
hi all,
I have a workbook with a hyperlink to another workbook.
Some cells from workbook2 are linked into workbook1 (eg =[Book2]Sheet1!$A$3). But sometimes when I open workbook2 with the hyperlink in workbook1 excel doesn't recognize workbook2. When this happens I can open workbook2 by using edit links -> open source and then everything is working fine. What can I do about the unpredictable behaviour of excel?
I'm working with excel 2000
OS windows 2000 pro
Thanks in advance,
Fluppe
I found my problem.
The macro's work fine on the PC's who did have an office upgrade (only Service Packs and security stuff) earlier this week.
So I have to wait until all the PC are upgrated.
Big problems have small solutions:-)
Fluppe
Hi,
I'm using 2 workbooks 1 and 2. They are saved on my companies network. Workbook1 is always opened first because it contains macro's which are used in workbook2.
When I open workbook2 I get an error because Excel can't find the macro on workbook1 (Workbook1 is open).
I have to go to edit>links>change source to select workbook1 and now the macro's will work.
When I save and close the workbooks and I open the 2 workbooks on my
colleges PC (which is connected on the same network) I have to do the same action before I can use the macro's???
I made a Public Sub in a module in workbook1.
I assigned the macro on a cmdbutton in workbook2.
Any help is welcome!
Fluppe
Hi,
I think I want to change the regional settings when starting the specific excel file.
[HKEY_CURRENT_USER\Control Panel\International]
sDecimal="."
sThousand=","
It's possible to change the settings when you load the modAccesRegistry.bas in the file.
I have no idea how I can change these settings in VBA and returning the old
settings when the workbook is closed.
Can anybody help me?
Tanx alot!
Hi,
I have a workbook with a conditional formatting in cel $C$12 e.g. if Cell value is between 0.01 and =C$13-0.01 the pattern turns red.
If someone uses the workbook and enters a new value in C12 but his or here regional settings are different: sDecimal="," and sThousand="." than the conditional formatting isn't working.
How would you solve the problem?
Thanx very much,
Fluppe
Andy,
Now I can go on.
Tanx alot:biggrin:
Hi Andy,
Yes, that works perfect.
I want it a little bit different but I forgot to mention it.
I think I need a loop function because its not only range(“A3:E3”) that must have a conditional formatting but also range(“A6:E6”), range(“A9:E9”) and so on until range(“A300:E300”)
I don’t know how I can do that?
I tried to make something in sub Condformat2()
Hi,
See my attachement.
This is only the code for the conditional formatting I posted already.
The same problem occurs.
Thanx,
Fluppe
Hi Andy,
I have tried already to change the order
but that doesn't help.
I have tried the macro for formatting one cel and that worked. But when I adjusted the code for more than one cell it failed??
Is it possible that it fails because I'm using it in a loop?
Hi,
I’m using the following simple conditional formatting: when a cell contains a number it must change in a grey pattern.
I’m using the macro below but it doesn’t apply the Pattern and the PatternColorIndex. The Colorindex works perfect. Does anybody know what is wrong in this code?
For c = 1 to 5
Range(Cells(c, 5), Cells(c, 7)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(E" & c & ")"
Selection.FormatConditions(1).Font.ColorIndex = 55
With Selection.FormatConditions(1).Interior
.ColorIndex = 0
.Pattern = xlGray50
.PatternColorIndex = 15
End With
c = c+1
Next c
Thx,
Fluppe
thanx Neale,
I wish that everything is as easy as this.
Maybe its due to the early monday morning!
:wink1:
Hi, I have a little problem with the following code:
Sub aanmaak_folder()
Dim i_folder As String
Dim i_cel As Integer
Dim a As String
Dim b As String
Const OverwriteExisting = True
Set obj = CreateObject("Scripting.FileSystemObject")
Set objfile = CreateObject("Scripting.FileSystemObject")
i_cel = 1
While i_cel < 6
i_folder = Cells(i_cel, 1).Value ‘values of cell a1:a5 are the names of the folders
a = "C:\" & i_folder
Set objFolder = obj.CreateFolder(a)
b = "C:\" & i_folder & "\" & i_folder & ".xls" ‘the file name must be the same as the folder name
objfile.CopyFile "C:\tag\copyfiletest.xls", "b", OverwriteExisting
i_cel = i_cel + 1
Wend
End Sub
This code makes automatically dir. with the names stored in cells a1:a6. (“tag1”,”tag2” to “tag5”)
This is working well. But the workbook copyfiletest.xls must be copied under each new folder and renamed with the same folder name. Here is the problem: there are no files copied only the folders are created.
I don’t know where the problem is?
Eg "C:\test\copyfiletest.xls"
-> "C:\tag1\tag1.xls"
-> "C:\tag2\tag2.xls"
thanx,
Fluppe
Hi,
I have a workbook 500kB and it takes a certain time before it is saved. This workbook will also be used by people using a pentiumII. So it takes probably double the time.
Is it possible to add a progress bar when the workbook is saved so that the people don't have to panic when it takes some time and how can I do that?
Thanx,
Fluppe
Thanx Andy:))