Posts by Fencliff

Re: Password Protect A Macro At Run

Create a userform with a text box. You can set a password character property to "*".

Re: Working With Values N Dynamic Arrayi

I have absolutely no idea what you are trying to do, but here is an example of how to iterate a 2d array.

Dim av2dArray() As Variant
    Dim lCol        As Long
    Dim lRow        As Long
    
    av2dArray = Sheet1.Range("B2:C15").Value
    
    For lCol = LBound(av2dArray, 2) To UBound(av2dArray, 2)
    
        For lRow = LBound(av2dArray, 1) To UBound(av2dArray, 1)


            Debug.Print "(" & lRow & "," & lCol & ")", av2dArray(lRow, lCol)


        Next lRow
        
    Next lCol

Re: Passing Parameter To Vba From C++

Hi,

I don't have much experience with C++, so can't help you with actual code. From what I can remember, this is fairly easy through MFC, but you might have to do some marshalling because all native C++ data types are not compatible with Excel's. In any case, you could use Excel's COM automation by connecting with CreateDispatch and instead of using Workbook_Open() you could run the VBA code directly using Application.Run and pass the parameters from your DLL.

Search MSDN or google for MFC automation with Excel, should get you started.

Re: Handling Of #div/0! In Select Case Statement?

Quote from Robin1981

Write

Code

On Error Resume Next

at the start of your sub

Bad idea.

Try this instead:

If Not Application.WorksheetFunction.IsError(rngCell) Then
    
        'Select Case ...
    
    Else
    
        rCell.Interior.ColorIndex = 15
        
    End If

Re: Calculation Values Of True And False

In spreadsheet formulas, yes.

Try it yourself:
=ABS(TRUE)
=ABS(FALSE)

Ok, ok - TRUE could also be -1, but take my word for it, it's not

Re: Inserting Check Mark By Cliking On A Cell

That's correct. What's wrong is hard to say.

- You pasted the code in the wrong place
- Excel's events are disabled - restarting Excel would help
- You are debugging some other code.
- Million other reasons.

If you send the workbook where you pasted this, maybe somebody can take a look at it.

Re: Password Protect A Macro At Run

That's a fairly vague requirement. What level of security do you need? Do you need individual passwords for different users? Etc.

Here is the simplest possible implementation, and not secure by any measure, but will keep the average user away:

Dim sPassword As String
    sPassword = "Hunter-2"
    
    If Application.InputBox( _
        Prompt:="Please enter the password", _
        Title:="Password", _
        Type:=2) <> sPassword Then
        
        MsgBox "Wrong password!"
        Exit Sub
    
    End If

Re: Labels Content Equal String Based On Scrollbar Value

Hi,

Unfortunately you can't build variable names like strings. In this case you need to use an array.

Option Explicit


'Instead of individual variables, let's use a variant array
Public Meddelander() As Variant


Private Sub UserForm_Initialize()


    On Error Resume Next
    Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False
    Application.ScreenUpdating = False
    Workbooks.Open "V:\alla\Beredning\Kontrollsystemet\Kontrollsystemet.xls", ReadOnly:=True
    Sheets("Meddelanden").Activate
    
    'Variant arrays can be populated simply by transposing the range
    Meddelander = Application.WorksheetFunction.Transpose(Range("B2:B10").Value)


    Workbooks("Kontrollsystemet.xls").Close SaveChanges:=False
    Application.ScreenUpdating = True
    
End Sub


Private Sub ScrollBar1_Change()


    Dim i As Long
    
    'This On Error Resume Next you had here is the only thing keeping this
    'code working if ScrollBar1.Value = 0 or > 7. Better way to handle would
    'be to check the value and act accordingly, but I'm going to leave this
    'for you for now.
    
    On Error Resume Next
    
    i = ScrollBar1.Value


    Label1 = Meddelander(i)
    Label2 = Meddelander(i + 1)
    Label3 = Meddelander(i + 2)
    
End Sub

Re: Remove All Labels In A Frame

Dim fra As MSForms.Frame
    Dim lbl As MSForms.Label
    Dim i As Long
    
    Set fra = Me.Frame1


    For i = fra.Controls.Count - 1 To 0 Step -1
    
        On Error Resume Next
        Set lbl = fra.Controls(i)
        On Error GoTo 0
        
        If Not lbl Is Nothing Then
            fra.Controls.Remove i
            Set lbl = Nothing
        End If
        
    Next i

Just remember that you can only delete controls created at runtime. Controls created in design mode can not be removed programmatically. You could still hide them, though.

Re: Inserting Check Mark By Cliking On A Cell

Right click the sheet tab you want to insert the functionality to, and choose "View code..."

Paste the code there.

Re: Inserting Check Mark By Cliking On A Cell

The character "a" in the font Marlett displays a check mark. The font is available in all versions of Windows, so you should be able to use it safely.

The click part here is a little more complicated, because Excel doesn't expose any events to determine a cell being clicked. You can either choose to use DoubleClick or SelectionChange Events. DoubleClick has the disadvantage that it might not be entirely obvious for the user, and SelectionChange will also place the check mark if user navigates to the cell using the keyboard.

In either case, place this code in the sheet module where you want this to take effect.

DoubleClick:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    'Change this address to the area where you want the click to produce a checkmark
    Const sCheckAddress As String = "A1:B10"


    Dim rngIntersect As Range
    
    On Error Resume Next
    Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target)
    On Error GoTo 0
    
    If Not (rngIntersect Is Nothing) Then
        Target.Font.Name = "Marlett"
        Target.Value = "a"
    End If


End Sub

SelectionChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    'Change this address to the area where you want the click to produce a checkmark
    Const sCheckAddress As String = "A1:B10"
    
    Dim rngIntersect As Range
    
    If Target.Cells.Count = 1 Then
      
        On Error Resume Next
        Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target)
        On Error GoTo 0
        
        If Not (rngIntersect Is Nothing) Then
            Target.Font.Name = "Marlett"
            Target.Value = "a"
        End If
        
    End If
    
End Sub

Re: Change Unc Links To Drive Letter Mapping

What's wrong with UNC-addresses? They are used for a good reason.

Re: Tab Delimited File Saves Commas As Dot

I've heard the horror stories of Swedish Excel. I am living in Denmark myself, but using English Excel, thank god!

For that same reason I have no way of testing this, but have you tried saving with FileFormat:=xlCurrentPlatformText ?

Re: Keyboard Shortcut Causes Error

Never mind what he is selecting, he is copying the entire sheet onto the clipboard! Scary thought, my laptop would die twice during that macro.

Re: Userform Validation

I would reconsider the lower boundary. What if the user wants to erase the current value, and gets an error? The below code will show and error when the user reaches 51 characters, and trim off the extra bits. If you want validation for zero-lenght strings, I would do it when the "OK" button or similar is clicked

Private Sub TextBox1_Change()


    If Len(Me.TextBox1.Value) > 50 Then
        MsgBox "Error!"
        Me.TextBox1.Value = Left(Me.TextBox1.Value, 50)
    End If
    
End Sub

Re: Breakdown Number Of Part Matches

Hi,

I think it would be fun to write the bartering logic, but as I said, unfortunately I don't really have the time to do this at work, and you're not going to get me anywhere near a spreadsheet at home

If this is something you do annually and on other infrequent occasions, I think that would be overkill anyway. It took me 5 minutes to dish out the clients by hand, so given even you would have to do this up to 5 times a year, and this logic would take 2-3 hours to write, the ROI just isn't there, unless you plan to do this for the next decade or so

Re: Breakdown Number Of Part Matches

I took a look at it also, and, well... to crunch this brute force would be ridiculous, especially in VBA!

I don't really have time to implement this (sorry!) but the best way I can figure to solve this would be start by assigning the clients to CSRs according to method I provided earlier. Given a reasonable margin of error, let's say a maximum variance of 10 clients. After that you can exclude those CSRs that are really close to the "optimal" sum, that is Total Clients/5 +- 2. If any CSRs do not fit into this, you can start bartering letters between CSRs so that the deficient ones put out a buying signal, and the ones in excess give a selling signal. By that logic you could have a reasonable amount of combinations of compare and trade until all CSRs are within the limit.

Re: Formula Miscalculations

I would guess you have set formulas to calculate manually in Options. Check the Options > Calculation tab.

Re: Data Editor On Userform

Did you really just nonchalantly ask if somebody would do this for you?

Maybe you start writing code yourself, and when you get stuck with an actual problem, ask?

Re: Find Cell Value In PDF File

Like I said, I don't have Acrobat or Acrobat Reader installed, so unfortunately I can't take a look any deeper. The registry mess the reader creates was fun enough to clean the first time around.

Take a look at http://support.adobe.com/devsup/devsup.nsf/docs/51011.htm

That targets older versions of the typelib (4.0, 5.0, the current version I think is 8.0) and the AcroAVDoc class seems to be named CAcroAVDoc.

In general though, Google is your friend.