Posts by dodger7

Re: Set Desktop Background

Thanks AAE, I had a look but its a bit out my depth, and I dont have VB installed just excel but thanks anyway

Re: Fv Function

in your formula you need to specify whether the payment is made at the start of the period or the end of the period. You do this by specifying a type within the formula. In your case its defaulted to the end of the period (type 0), hence why its not added interest on for year 2. You need to change it to type 1, which means payment is made at start of period.

The syntax is:

=FV(Rate, No. of Pymnts, £ amount, current value, type)

so, try this:

=FV(A2,A3,A1,,1)

HTH
Jamie

Re: Convert Much Text In Workbook To Hyperlinks

I dont know if theres a quicker way, but the normal way is to select the cell/row/text that you want to be the link, then go to insert>hyperlink (or ctrl + k) then choose place in this document from the left menu, then choose the cell reference/sheet from the right

Re: If Formula

=IF(OR(A1="B and D"), "Correct",IF(OR(A1="B + D"), "Correct", "Wrong" ))

Hi Guys

Im designing a desktop background for my office, and want to roll it out once complete.

Rather than trying to email the image out and get people to do it manually, is there anything in vba i can do to assist in this? I know its asking a bit much, but ive been surprised in the past with how versatile vba is! (i.e mapping network drives)

I asked the IT guys to do it over the server, but they started ranting about screen resolution, priority of other jobs etc etc.

Thanks in advance for any help
Jamie

Re: Update Data In Userform

check out my database example, it covers your question. Its open source, feel free to use, adapt etc to your needs.

You can create, edit and delete entries etc.

It can be found here:

http://www.ozgrid.com/forum/showthread.php?t=77326

HTH

Jamie

Re: Adding Dashboard Functionality

removing the scroll bars dosnt stop users scrolling within the sheet.

In the "this workbook" module have the following code:

Private Sub Workbook_Open()


Sheet1.ScrollArea = "A1:Q40"


End Sub

Re: Pause While Sending Keystrokes

sendkeys has never been reliable, specially for what your trying to do. (nothing is really)

You could try the sleep API as below:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


 ' first select the correct printer
Application.ActivePrinter = "\\hagen\Copy Room Savin 4045 PCL6 on Ne05:" 
sleep 1000
 ' next set change printing options, presses ok to print
Application.SendKeys "%fp%r+{TAB}{RIGHT}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{UP}{UP}{UP}{DOWN}{DOWN}~%c3~", True 
sleep 1000 
' next set change printing options to other color tray, presses ok to print
Application.SendKeys "%fp%r+{TAB}{RIGHT}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{UP}{UP}{UP}{UP}{UP}{UP}{DOWN}{DOWN}{DOWN}~%c1~", True 
sleep 1000

1000 = 1 second.

HTH

Re: Pause While Sending Keystrokes

the simplest way to do it is:

application.wait(Now + Timevalue("0:00:01"))

obviously adjust the second accordingly. If you require a pause of less than a second let me know

HTH[hr]*[/hr] Auto Merged Post;[dl]*[/dl]also, rather than doing {TAB}{TAB}{TAB} just do {TAB 3} etc will make it tidier and easier to read

Re: Paste From Listbox To Next Available Cell

could you post an example?

Re: Disable Editing Cells Without Protecting Worksheet

can you not just wait till noone is in it, make it unshared, protect the cells required then re-share it?

Re: Upon Opening A Form Do This

write your code under Private Sub userform_initialize()

Right click the form when in vba editor, and click view code. Create a new sub as above.

Re: Return A Result From A Range Where Criteria Is Met

Hi

I would lay it out different (personally).

See the attached example, using the vlookup formula

Jamie

Re: Visibility Based On Value Of Multiple Column ListBox

Hi There

Thanks for all input, Andy sorry I took so long to answer. Yes It turned out I only wanted it visible if the text appears in the selected row, so Daves works as needed. However, previously I had wanted it to be visible if it appeared, selected or not so Ians worked for that need.

Thanks again

Hi Folks

Ive got a listbox on a userform with 4 columns. Ive also got a hidden command button on this form. I want the command button to become visible if, at any point, certain text appears in the fourth column of this listbox. This could be any row of the listbox, but will always be the fourth column if it does appear.

Any suggestions would be greatly appreciated.

Thanks

Re: Load Data Meeting Condition Into Listbox

Thanks reafidy. I apologise that I never picked this up with Andys reply.

Ill read more carefully the next time
Thanks again, your time is appreciated

Re: Load Data Meeting Condition Into Listbox

Thank you both Andy and Dave for your replies. Makes sense.

However on my attached example, searching by name works fine - it returns all the results. Its just by code that dosnt work. Ive printed off the subs for search by name and search by code and they seem to be the exact same (apart from different ranges to search, of course)

Any ideas why searching by name works ,but the search ny code only returns the one?

Thanks again

Hi guys

im using an adapted version of Roys database. Heres my code:

Private Sub cmbFindcode_Click()
Application.ScreenUpdating = False
Sheet3.Activate


    Dim strFind, FirstAddress As String   'what to find
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet3.Range("b8", Range("b65536").End(xlUp))
    strFind = Me.TextBox2.Value    'what to look for
    Dim f As Integer


    If Me.TextBox2.Value = "" Then
        MsgBox "Please enter a Fund code to search for"
        GoTo nullentered
    End If


    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .TextBox1.Value = c.Offset(0, -1).Value
                .TextBox2.Value = c
                .TextBox3.Value = c.Offset(0, 1).Value


               f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress




            If f > 1 Then


                If f > 50 Then


                    MsgBox "There are more than 50 funds with """ & strFind & """ as part of the code. Please narrow your search criteria"
                    Call CommandButton2_Click
                Else
                    MsgBox "There are " & f & " funds with " & strFind & " as part of the code"
                    Me.Height = 489


                End If
                
                           Me.cmbFind.Enabled = False
    Me.cmbFindcode.Enabled = False
            End If
        Else: MsgBox strFind & " not listed"    'search failed
        
           Me.cmbFind.Enabled = True
    Me.cmbFindcode.Enabled = True
        End If
    End With
    Call cmbFindcodeAll_Click
nullentered:
Sheet2.Activate
End Sub


Private Sub cmbFindcodeAll_Click()
    Dim FirstAddress As String
    Dim strFind As String    'what to find
    Dim rSearch As Range     'range to search
    Dim fndA, fndB, fndC As String
    Dim head1, head2, head3 As String    'heading s for list
    Dim i As Integer
    i = 1
    Set rSearch = Sheet3.Range("b8", Range("b65536").End(xlUp))
    strFind = Me.TextBox2.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            'load the headings
            head1 = Range("a7").Value
            head2 = Range("b7").Value
            head3 = Range("c7").Value


            With Me.ListBox1
                MyArray(0, 0) = head1
                MyArray(0, 1) = head2
                MyArray(0, 2) = head3


            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -1).Value
                fndB = c.Value
                fndC = c.Offset(0, 1).Value




                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC


                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress


        End If
    End With
    'Load data into LISTBOX
    Me.ListBox1.List() = MyArray
Sheet2.Activate
End Sub

This works fine for the first part - i.e it loads the data into the textboxes, and loads the headers and first search result into the listbox. However it always only loads the first search result, not all.

Does anyone know why this might be? If its not clear I can post a simplified example of the working spreadsheet.

Thanks in advance[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Ive uploaded an example to assist anyone kind enough to assis me

Thanks

Re: Calculating Data From Each Sheet In A Workbook

cheers buddy

ill get a look at that just now

Hi Guys

Im creating a workbook for my office. The workbook has a sheet for each user. The user chooses a channel from the drop down list, then complete the next 5 fields (must add to 100). I have a totals sheet where I want to summarise the data from the whole workbook as per the template on the totals sheet.

Ive attached my example.

Thank you very much for any input