Posts by DrGuru

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.

    Good morning everyone,

    I am certain that the solution for this is probably something so simple that I will kick myself for not being able to figure it out.

    Basically, what I wanting to do is this:

    I am building a large front-end application for my department at work. It uses a modified version of Dave's PasswordBook for logging users into the system.

    Is there a way, either by modifying the form properties or through VBA coding to set it up so that if a user presses ENTER in either the username or password textboxes, that it trigger the OK commandbutton just as though the user clicked on it?

    Good morning,

    Pursuant to Roy's answer, if the macro is to be used in several workbooks in Excel, then perhaps you could create the macro in Personal.xls as the macros saved here are available to all workbooks created in Excel.

    Hi Xerde,

    I know that you have found a solution that meets your needs, but I thought I would throw this in.

    Given the error that you received, it sounds like you had in your code, something to the tune of Sheet2.Select or something similar.

    You cannot select sheets in Excel that are hidden... that is the reason for this particular error. If you remove the "Select" statement, then Richie's suggestion should work fine for you.

    Good evening Dave,

    Not sure if you saw the last reply...

    I have tried all the posted suggestions and the only one that works... for now... is Richie's suggestion for speeding up the process of having to open each source workbook.

    Unfortunately, this will only work for a short time, as the number of agents that we are processing accounts for is constanting increasing. Given the limitations of the system that I am using at work, I know that using this method will eventually result in either Excel shutting down or the system crashing due to lack of memory/system resources.

    My work system is running Excel 2000 on Win98. Any suggestions on how I can get this to work without having to open each source workbook?

    Good evening Chas,

    Not too sure if this helps or not, but as a suggestion, perhaps you could turn your application into an Excel add-in. I think by doing that, you would satisfy the legal grounds for being able to sell it.

    Good morning Dave,

    Thanks for the reply. I have tried all the suggestions that have been posted so far, but to no avail.

    I did try the one as well that you provided the link for. Reason why I couldn't get it to work is because it requires that a small snippet of code be placed in the originating workbook, which, unfortunately, I am unable to do.

    Any other suggestions?

    I stand corrected in terms of the cell limits. However Richie, I have found that when you exceed the cell width limit, it makes no difference if the next column over is empty or not... it still won't show all the text... unless you click on the cell, at which time all the text shows in the formula bar.

    Good morning Neal,

    I too have experienced this problem in the past. Based on my analysis of what was happening, cells do indeed have a character limit of approxiamately 1090 characters.

    Unfortunately, I have yet to figure out a way to grab the first 1000 characters of a string, paste them into their target cell, and then take the remaining text and paste it into the next cell down.

    I am sorry that I wasn't able to provide a full response.

    Good morning,

    Well, I tried your formula Richie and unfortunately it did not produce the desired results.

    Based on the example that I provided above, if a person were to borrow $80.00 for 1 week (7 days), then their interest payable would be $0.65.

    Good morning everyone,

    One of the modules in the project I am currently working on is used to calculate interest payable on a loan. This would be easy for me to do if it were based on a simple interest rate... but it isn't.

    If my interest were calculated on the basis of X Amount per Y Amount per Z Time, how would I calculate? As an example, let's say that interested is calculated on the basis of 65 cents per 80 dollars per week. If the loan were for 290 dollars and a duration of 9 days, how would I write the formula to calculate the interest?

    Good morning Dave,

    Thanks so much. It works perfectly. I wish foresight could be just as powerful as hindsight, because I could have saved myself hours of work in modifying code if I had just implemented things like this into my project from the start. :biggrin:

    Good morning Dave,

    I tried your second code suggestion, but to no avail. I placed the code in a UserForm_Activate procedure.

    The only modifications I made are as follows:

    1) Changed the path
    2) Changed .FileType from msoFileTypeExcelWorkbooks to "*.cpc"

    When I run the code, the combobox remains empty... what did I do wrong? :(

    Good morning Trees,

    Here is a copy of the code that was provided to me by Andy Pope. You should be able to easily modify it to be specific to your application.

    Option Explicit

    Private m_shtMyData As Worksheet
    Private m_lngActiveRow As Long
    Private m_lngMaximumRow As Long

    Private Sub LoadRecord()

    Dim strName As String

    strName = m_shtMyData.Name & "!"
    TextBox1.ControlSource = strName & m_shtMyData.Cells(m_lngActiveRow, 1).Address
    TextBox2.ControlSource = strName & m_shtMyData.Cells(m_lngActiveRow, 2).Address
    TextBox3.ControlSource = strName & m_shtMyData.Cells(m_lngActiveRow, 3).Address
    TextBox4.ControlSource = strName & m_shtMyData.Cells(m_lngActiveRow, 4).Address

    Me.Caption = "Record [" & CStr(m_lngActiveRow - 1) & "/" & CStr(m_lngMaximumRow - 1) & "]"
    End Sub

    Private Sub MoveBack()

    m_lngActiveRow = m_lngActiveRow - 1

    cmdBack.Enabled = Not (m_lngActiveRow = 2)
    cmdNext.Enabled = (m_lngActiveRow <= m_lngMaximumRow)

    End Sub

    Private Sub MoveFirst()

    m_lngActiveRow = 2

    cmdBack.Enabled = False
    cmdNext.Enabled = (m_lngActiveRow < m_lngMaximumRow)
    End Sub

    Private Sub MoveLast()

    m_lngActiveRow = m_lngMaximumRow

    cmdNext.Enabled = False
    cmdBack.Enabled = (m_lngActiveRow &gt; 2)
    End Sub

    Sub MoveNext()

    m_lngActiveRow = m_lngActiveRow + 1

    cmdNext.Enabled = Not (m_lngActiveRow = m_lngMaximumRow)
    cmdBack.Enabled = (m_lngActiveRow &gt; 1)

    End Sub

    Private Sub cmdBack_Click()
    End Sub

    Private Sub cmdFirst_Click()
    End Sub

    Private Sub cmdLast_Click()
    End Sub

    Private Sub cmdNext_Click()
    End Sub

    Private Sub CommandButton1_Click()
    Unload Me
    End Sub

    Private Sub UserForm_Initialize()

    Set m_shtMyData = Worksheets("Sheet1")
    m_lngMaximumRow = Application.WorksheetFunction.CountA(Range("A:A"))

    Label1.Caption = m_shtMyData.Cells(1, 1).Text
    Label2.Caption = m_shtMyData.Cells(1, 2).Text
    Label3.Caption = m_shtMyData.Cells(1, 3).Text
    Label4.Caption = m_shtMyData.Cells(1, 4).Text


    End Sub

    Good morning everyone,

    I am working on a project wherein one of the modules is for me to be able to view the usage summary log in a userform textbox. I am now wanting to slightly modify it so that a new log file is created each month. That part will be very easy for me to do.

    My question is this:

    Is it possible to set up a combobox on my log viewer form that will show the contents of the folder where the log files are saved, allowing me to choose which log file to view?

    Any help will be greatly appreciated. :)