Posts by Fluppe

Important Notice


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.

    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?


    Thanks,
    Fluppe

    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?


    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

    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?

    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

    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