Posts by Fluppe

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

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

Re: shapes

Oké thank you! It's working grate.

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

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"
-&gt; "C:\tag1\tag1.xls"

-&gt; "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:))