Posts by Fluppe

    Hi, I use this code as a routine to create buttons on workbook("meetpunten.xls").worksheets("sheet1")

    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?


    Hi all,

    I use the following macro to create a series of commandbuttons:

    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?


    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,

    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:-)



    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!


    I think I want to change the regional settings when starting the specific excel file.
    [HKEY_CURRENT_USER\Control Panel\International]

    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!

    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,

    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 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?

    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.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


    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


    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"


    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?