VBA Form in Excel

[turps]

Hi

I have created an Excel Workbook that I populate by a form created in vba. Within this form have combo boxes that are populated when the form is displayed.

I have two buttons one is to add an entry and the other is to edit entry. The edit entry button picks the data from the specfic row that I am on and then should populate the form when it opens.

The problem that I have is that when I click on the edit button the form does not pick up the data from the worksheet. I cancel the form using a cancel button do it again and then it works.

Does anyone know why this might be?

Thanks



Regards

David

 

[turps]

Hi

Here is my code.

What should happen is that when the editentry button is selected data from the spreadsheet should fill the form. the first time it doesn't, when I cancel and try again all is fine.

Any ideas would be good.

Thanks

Private Sub UserForm_Initialize()
    If flag = 1 Then
        editdata
    Else
        adddata
    End If
   
    
End Sub

Sub editdata()


   
    srow = ActiveCell.Row
    cmbOffice = Cells(srow, 1)
    txtClientName = Cells(srow, 2)
    cmbBC = Cells(srow, 3)
    txtDBno = Cells(srow, 4)
    txtDeadline = Cells(srow, 5)
    txtDatein = Cells(srow, 6)
    txtTimein = Cells(srow, 7)
    txtFirstCheckStart = Cells(srow, 8)
    txtFirstCheckFinish = Cells(srow, 9)
    txtCheckersInitials = Cells(srow, 10)
    txtECT = Cells(srow, 11)
    txtShift = Cells(srow, 12)
    cmbDepart = Cells(srow, 13)
    txtDocREf = Cells(srow, 14)
    txtDocTitle = Cells(srow, 15)
    txtNoOfPages = Cells(srow, 16)
    cmbProofread = Cells(srow, 17)
    cmbDocproduction = Cells(srow, 18)
    txtReNegDeadline = Cells(srow, 19)
    txtReCheckStart = Cells(srow, 20)
    txtReCheckFinish = Cells(srow, 21)
    txtReCheckCheckersInitials = Cells(srow, 22)
    cmbReturnedby = Cells(srow, 26)
    txtComments = Cells(srow, 27)
    
    ComboBoxFill
  

End Sub

Regards

David

 

[SkipVought]

Reference the SHEET on which you are working

Sub editdata()


    With Sheets("YourSheetName")
        srow = .ActiveCell.Row
        cmbOffice = .Cells(srow, 1)
        txtClientName = .Cells(srow, 2)
        cmbBC = .Cells(srow, 3)
        txtDBno = .Cells(srow, 4)
        txtDeadline = .Cells(srow, 5)
        txtDatein = .Cells(srow, 6)
        txtTimein = .Cells(srow, 7)
        txtFirstCheckStart = .Cells(srow, 8)
        txtFirstCheckFinish = .Cells(srow, 9)
        txtCheckersInitials = .Cells(srow, 10)
        txtECT = .Cells(srow, 11)
        txtShift = .Cells(srow, 12)
        cmbDepart = .Cells(srow, 13)
        txtDocREf = .Cells(srow, 14)
        txtDocTitle = .Cells(srow, 15)
        txtNoOfPages = .Cells(srow, 16)
        cmbProofread = .Cells(srow, 17)
        cmbDocproduction = .Cells(srow, 18)
        txtReNegDeadline = .Cells(srow, 19)
        txtReCheckStart = .Cells(srow, 20)
        txtReCheckFinish = .Cells(srow, 21)
        txtReCheckCheckersInitials = .Cells(srow, 22)
        cmbReturnedby = .Cells(srow, 26)
        txtComments = .Cells(srow, 27)
    End With
    ComboBoxFill
  

End Sub

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[rmikesmith]

David,

How/Where is the variable flag declared and where is its value set? You mention two buttons to add or edit an entry. Are these on your worksheet and what code runs when you click them?


Regards,
Mike

 

[turps]

Hi Mike

I have two buttons on a toolbar the code is below.

the var flag is declared as public on a seperate sheet.

Sub addentry()

    flag = 0
    
    Checkers.Show
End Sub


Sub editentry()
       
    flag = 1
    
    srow = ActiveCell.Row
    erow = srow
    Checkers.Show
    
    
End Sub

Hi SkipVought

I have a different worksheet for each month how can I use With Sheets("YourSheetName") code as each month it will change and I don't fancy changing it each month.

Thanks



Regards

David

 

[SkipVought]

Why do you have a separate sheet for each month? Similar data ought to reside in a common table. Then when you REPORT the data, you can report by month or whatever.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

This is one of the mistakes that most spreadsheet designers make when capturing data.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[turps]

I would love to use access or some other DB system to create it in but I have to work with what we can use.

The reason that I use different sheets is on average each mth about 1500 rows of data are added. Having it in one sheet would make it very unuseable for the people in my office.

Any help would be useful.



Regards

David

 

[SkipVought]

So, you add 1500 rows each month. Thats 18,000 rows per year -- a very modest table size.

There's AutoFilter, Pivot Table, Subtotal just to name 3 tools for "zooming in" on a month's worth of data.

Having similar data on separate sheets is about as useful as having data on a sheet of paper in a file cabinet. Surely, someone has asked for a weekly or quarterly or annual report of this data (or they will) and then 'yer up the crik'. The data reporting tools in Excel could generate those kinds of answers in MERE SECONDS (as long as the data is in a common table and not spread across sheets)

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[turps]

Hi Skip

I have tried this and it still does not pick the data up the first time.

Regards

David

 

[SkipVought]

Then try this...

    With Sheets("YourSheetName")
        .Activate
        srow = ActiveCell.Row
....

BTW, you can assign a sheet name and then select it in your code

sSheetName = Input("What Sheet")
    With Sheets(sSheetName)
        .Activate
        srow = ActiveCell.Row

or specify the ActiveSheet.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}