Posts by DrGuru

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 Douglas,

Not sure if this helps, but try removing the last ) as you have four ('s in your formula and five )'s.

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 evening Dave,

Unfortunately, I am unable to place formulas in the source workbooks either. In light of this, is there still a way that it can be done, either by formula or VBA without having to open each source workbook?

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,

Thanks to all for your help. It is working perfectly now.

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 and Ivan,

Thanks so much for you help. It works perfectly. :cool: :biggrin: :cool:

Good morning Ivan,

Thanks for your suggestion. I will try it out and let you know how I make out with it.

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:

Ok, got that one figured out, but just one more quick question:

Is there a way to have it only display the filename without the path?

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
LoadRecord

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

End Sub

Private Sub MoveFirst()

m_lngActiveRow = 2
LoadRecord

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

Private Sub MoveLast()

m_lngActiveRow = m_lngMaximumRow
LoadRecord

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

Sub MoveNext()

m_lngActiveRow = m_lngActiveRow + 1
LoadRecord

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

End Sub

Private Sub cmdBack_Click()
MoveBack
End Sub

Private Sub cmdFirst_Click()
MoveFirst
End Sub

Private Sub cmdLast_Click()
MoveLast
End Sub

Private Sub cmdNext_Click()
MoveNext
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

MoveFirst

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.