Posts by RichardS

Re: Prevent UserForm From Moving

Thanks again Dave.

On further investigation, I don't thnk it's a UserForm but an Excel 5 Dialogue Sheet, so there isn't actually a CommandButton_Click() event (If that's what it's called). This is the code I have

Sub DataInput()
Application.ScreenUpdating = False
' Display the Data Input Form
    If DialogSheets("InputForm").Show Then
        Transfer_Data ' Call the code to transfer data
    End If
' Return to the menu
    Sheets("Menu").Select
End Sub










Sub Transfer_Data()
'
' Transfer_Data Macro
'


'
' This section transfers the data from the data input form to the Input sheet


    Dim Field1 As Date
    Field1 = DateValue(DialogSheets("InputForm").EditBoxes(2).Text) ' Req Date
    Sheets("Input").Cells(2, 2) = Field1
    Field2 = DialogSheets("InputForm").EditBoxes(3).Text 'Req No
    Sheets("Input").Cells(2, 3) = Field2
    Field3 = DialogSheets("InputForm").EditBoxes(1).Text 'Quantity
    Sheets("Input").Cells(2, 5) = Field3
    
' This section transfers the data from the Input sheet to the Data sheet


    Sheets("Data").Visible = True
    Sheets("Input").Visible = True
    Sheets("Data").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown ' Insert a row at the top of the database
    Range("A3:N3").Select
    Selection.Copy 'Copy all the formulae etc up to the first row
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    Sheets("Input").Select
    Range("A2:E2").Select
    Selection.Copy 'Copy  the input data to the Data sheet
    Sheets("Data").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Data").Visible = False
    Sheets("Input").Visible = False
        DataInput 'call the input form again"
End Sub

Probably not very pretty or efficient, but it does what I need.

Is there some code I can put at the start of this that will determine where on the screen the Dialogue box is displayed?

Cheers

Richard

Re: Prevent UserForm From Moving

Hi Dave,

Thanks for that. Haven't tried it yet, but I have discovered something else interesting. The UserForm stays where you drag it to on some PC's, which are Acer workstations we have had for a while. The ones where it moves are all brand new Dell workstations. I'm thinking it is unlikely to be hardware related, but more likely due to the image Dell used to create all these workstations ( we had 100 created at the same time). So I still think it is a software setting somehwere in Office, unless someone has heard of hardware settings that would affect this.

Cheers

Richard

Hi all,

I have a spreadsheet with a User From that transfers data from the user form to a sheet each time OK is pressed. On my workstation, the form stays in the middle of the screen. On the PC where it is in production, it moves to the left of screen a little each time OK is pressed until it is hard up against the edge. We recently moved to Excel 2003, and I'm wondering if there is a setting in Excel that contols this.

Cheers

Richard

Hi all,

I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.

=TRUNC((B1-A1)/365.25)&" Years "&ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"

Just wondering in there is a more efficient way of doing this.

Cheers

Richard

Re: Paste Range Names

Hi Tom,

Thanks for the quick reply. That does work, thanks heaps. Now I've got another issue. The source data isn't quite right either. No easy way out of this I'm afraid. I'm going to have to manually update the destination workbook. But at least once I've done that once, I can use your solution for the remaining 14 workbooks that need fixing. Thanks again.

Richard

Hi all,

I need to copy the contents of an entire sheet from one workbook to another, including the named ranges in that worksheet. The destination workbook has formulae that refer to these named ranges in a sheet in that workbook, but the data in the named ranges is incorrect. I want to replace that data with the new data. Problem is, the data is not the same size, so the existing named ranges refer to the wrong ranges if I just copy the data across.

Hope that makes sense.

Richard

Re: Poll: Do you use "Edit Directly In Cell"

If it's annoying, just click in the formula bar and edit there. That's what I do for long formulae.

Cheers

Richard

Re: Dynamic Link closed workbooks

From Excel Help

Remarks 


If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

Richard

Re: column headings are numbers instead of letters

Tools menu, Options, General tab and un-tick the R1C1 Reference style. (wring forum, BTW)

Richard

Re: date formulas for a given month

Try a Pivot Table, group and outline the date by month.

Richard

Re: Calculating Daily Averages

Try a Pivot Table. The field settings for the data will be average, then group and outline the date/time on a daily basis.

Richard

Re: Multiple Checkboxes

Are your checkboxes from the Forms menu? If you right click on the check box and select Format Control, the check box can be linked directly to a cell. If it's from the Control Toolbox, right click and select properties, and there is a linked cell property there also. Or am I barking up the wrong tree.

Richard

Re: Linking cells to closed workbooks

Are all the files in the same directory? You could perhaps consider Data Consolidation.

Re: Using Calendar Control

Hi Roy,

Out of curiosity, why is the calendar control unreliable. I don't use it anywhere, just curious.

Richard

Re: Demographics of Our Members

Loc: Western Victoria, Australia
Age: 44
Prof: Accountant/Business Intelligence

Re: $ 3.00 times 40%=1.20, 3.00 - 1.20 what formula??

or

=G9*(1-B9)

Richard

Re: Data form Dropdown lists

Hi Booger,

You're right, this is a native Excel data form. In order to have drop downs, you would need to create a User Form, and write VBA code to add entries to your database.

Alternatively, could the data be entered directly into the database? You could then use data validation to create drop downs directly on the sheet. The main issue with this option is that if there are other functions based on the database, the named database range is not automatically extended.

Richard

Re: Transfer USERFORM data to Worksheet

Thanks Roy. I recorded most of the copy code, so I'll look at modifying as suggested. I am using a Diaolog Sheet. Should I be using a User Form?

Richard

SOLVED: Transfer USERFORM data to Worksheet

OK, I nutted that one out. It's the tab order on the form. So as you tab through the form, the first Edit Box is EditBoxes(1) and so on. This is controlled by right clicking on the form on the Dialog Sheet, and selecting Tab Order. I have another issue to do with Pivot Tables, but I might leave that until tomorrow.

Ta

Richard

Hi all,

I'm creating a new database in Excel, with a Userfrom interface. Most of it's going along OK, and I thought I'd be able to use some code from an earlier project, which I'm sure I got help with from here. Do you think I can find it though?

The issue is transfering text data from a form on a Dialog Sheet to a worksheet.

Sub Transfer_Data()
'
' Transfer_Data Macro
'


'
    Dim Field1 As Date
    Field1 = DateValue(DialogSheets("InputForm").EditBoxes(18).Text) ' Req Date
    Sheets("Input").Cells(2, 2) = Field1
    Field2 = DialogSheets("Input Form").EditBoxes(19).Text 'Req No
    Sheets("Input").Cells(2, 3) = Field2
    Field3 = DialogSheets("Input Form").EditBoxes(5).Text 'Quantity
    Sheets("Input").Cells(2, 4) = Field3
    Sheets("Data").Visible = True
    Sheets("Input").Visible = True
    Sheets("Data").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Range("A3:M3").Select
    Selection.Copy
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select
    Sheets("Input").Select
    Range("A2:E2").Select
    Selection.Copy
    Sheets("Data").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Data").Visible = False
    Sheets("Input").Visible = False
        
End Sub

I think it has to do with the EditBoxes(18) number. How can I determine what the number should be? When I click on the text box on the form on the dialog sheet, the name box says Edit Box 18, so that's why I used that number. I've missed a step here somewhere. I'm not at all confident with VBA, so I apologise in advance for my ignorance.

Richard