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.
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
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:))