Posts by gollem

Hi,

I think you better post the example to be clear enough.

Gollem

Hi,

try

Dim strFile         As String
    
    strFile = "X:\Scprod\inout\bloomberg\bonds\" & Format(Now(), "ddmmyy") & "SCDaskBonds.csv"
    Workbooks.Open strFile

Hope this helps.

Gollem

Hi,

welcome to ozgrid.

if I understand correctly you want to fill in a cell for example "A1", press a button and go to a worksheet in the same workbook linked to that data?

Or do you mean open another workbook? Be more specific.

I'm not sure this is what you want:

Select Case ActiveSheet.Range("A1").Value
        Case 32
            'select sheet1
            Sheets("sheet1").Select
            'If you want to open a workbook
            'workbooks.open "c:\test.xls"
        Case 64
            'select sheet2
            Sheets("sheet2").Select
    End Select

Gollem

Hi,

try

Opener = ActiveCell.Name.name

Don't forget to add a path(c:\,...) before you open your workbook.

Gollem

Hi,

it depends on the data and the things you wanna do.

For example you have on sheet1 some data and you want them in another layout on another sheet, you can do this with formules.

But I think an example is needed to have a good solution. Perhaps you can make a small example. Some data and the result...

I need more info to know what you want to do.

Gollem

Hi,

to select a heet in that workbook:

activeworkbook.sheets("sheet1").select

to retreive data from a cell:

activeworkbook.activesheet.range("A1").value

You can always check code by using the macro recorder, it's a good way to learn some simple commands.

If you want more detailed information, you should post a workbook and explain which data should be transferred.

Gollem

If this doesn't work you can upload your excel and I will take a look at it.

Gollem

Hi,

I've done a little check and it seems that the Val function you use only gives rounded figure. You should try cdbl.

TextBox1 = FormatPercent((cdbl(TextBox1) / 100), 1)

First test this, if this not work try:

TextBox1 = FormatPercent((cdbl(replace(TextBox1,".",",",1)) / 100), 1)

I don't know if decimals you become use . or ,. In the example I replace the . by ,. It could be that you have to switch the two.

Gollem

Hi,

Dave's solution is better. Use a textbox instead of the listbox, it's the same idea.

Gollem

Hi,

why not use a listbox and adapt the properties so it looks like a label?

(special effect => 0, backcolor => gray, ...)

Gollem

Hi,

check this:

if Rng2.value = ""

Gollem

I agree with WillR. I do most of the time Vba but in this case you better use the solution of WillR.

If you can do it with a simple formula => do it

Nice solution, I like it.

Gollem

Hi,

could you post the file, it would help.

Gollem

Hi,

the first thing that I notice:

txtbEffdate = CDate(txtbEffdate) is not necessary

If you use Cdate() it has to be added:

rngdefswap.Cells(iRow, 4).Value = Cdate(frmNewswap.txtbEffdate.Value )

You can also try to add this code:

rngdefswap.Cells(iRow, 4).NumberFormat = "mm-dd-\y\y\y\y"
rngdefswap.Cells(iRow, 4).Value = Cdate(frmNewswap.txtbEffdate.Value )

Perhaps the other guys have some other solutions.

Gollem

Hi,

I'm not sure what the problem is. When is the code executed you mentioned:

If ComboBox1.Value = "" Then
MsgBox "Please select a company", vbInformation, "Select Company"
End

When you close the form, when you press a button?

Gollem

Hi,

I don't know if you 've got a lot of programming experience but here's an idea:

You should be some code when workbook opens:
This code checks automatically every line of your sheet. You should built a loop to do this. The program should check the backcolor of the cell(if ... send mail, else do nothing) You also have to check if there is already an email send(see below). You don't want the program send an email every time you open your excel.

You should pay attention that when you send a mail you put an extra field to your column with for example "SEND".

This is just a basic thought.

Gollem

Hi,

how do you store the textbox value in the cell?

I assume something like this:

activesheet.range("A1").value = txtbEffdate.text

Try:

activesheet.range("A1").value = CDATE(txtbEffdate.text)

Change also the cell format before you test it.

Gollem

Hello,

do you mean the path of an active workbook?

ActiveWorkbook.Path

Gollem

Hi, just call:

TextBox1_AfterUpdate

Gollem

Hi, try this:

' ... cell.value =

cdate(txtbEffdate)

or try to put the date not in a string but a date variable.

Gollem