Combination Date and Funtion

[Gowodka]

Hi,

So i have 10 buttons on my userform and if i press them i need to find some data according to the button pressed. All the 10 buttons have 1 thing in common they all need the Date, Yearnumber, Montnumber and weeknumber

I could copy the code below 10 times but i wan't to make some sort of Function or subprocedure so whatever button i press it always calculate the date, yearNumber, MonthNumber and the weeknumber

I just don't understand how to do this, i'm quite new to this

    Public Datum As Date           'the current Date
    Public DatumMaand As Single    'number of the Month
    Public DatumWeek As Single     'number of the week
    Public DatumJaar As Single     'Number of the Year
    
    Datum = Date
    DatumMaand = Month(Datum)
    DatumWeek = DatePart("ww", Datum, vbMonday, vbFirstFourDays)
    DatumJaar = DatePart("YYYY", Datum, vbMonday, vbFirstFourDays)

Thanks in advance

 

[WinteE]

Create a sub in the module section with public variables where you store the week, year and month.
In the code for the button you enter

Call Sub
'enter whatever you want to do with the data in the variables

 

[Gowodka]

Thx,

I made a sub in a module called GlobalStuff there are some global variables in here and the Date code

But it still doesn't work, the code is the following

Sub ZoekDatum()
    Dim Datum As Date           'Datum
    Dim DatumMaand As Single    'MaandNummer
    Dim DatumWeek As Single     'WeekNummer
    Dim DatumJaar As Single     'JaarNummer
    
    Datum = Date
    DatumMaand = Month(Datum)
    DatumWeek = DatePart("ww", Datum, vbMonday, vbFirstFourDays)
    DatumJaar = DatePart("YYYY", Datum, vbMonday, vbFirstFourDays)
End Sub

Then i go to my userform and under a cmdbutton_click i put to code :

Private Sub cmdOkAenR_Click()

Call ZoekDatum

Worksheets("sheet1").Activate
RowCount = Workbooks("Back UP 4.xlsm").Sheets("sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("sheet1").Range("A1")
    .Offset(RowCount, 7) = DatumJaar
    .Offset(RowCount, 8) = DatumMaand
    .Offset(RowCount, 9) = DatumWeek
    .Offset(RowCount, 10) = Datum
End With

End Sub

The code executes but it won't put it in my workbook

 

[Gowodka]

If i use

Public Datum as Date

in stead of

Dim Datum As date

it gives me the compile error Invalid Attribute in Sub or Function

 

[WinteE]

Try something like this, found at http://en.allexperts.com/q/Excel-1059/help-8.htm

Sub SetDatum()
   Dim ctrl As OLEObject
   For Each ctrl In Sheet1.OLEObjects
       If ctrl.progID Like "*CommandButton*" Then
         Datum = Date
         DatumMaand = Month(Datum)
         DatumWeek = DatePart("ww", Datum, vbMonday, vbFirstFourDays)
         DatumJaar = DatePart("YYYY", Datum, vbMonday, vbFirstFourDays)
         ctrl.Delete
       End If
   Next ctrl
End Sub

Succes

 

[Gowodka]

Hoi, Ik zag dat je nederlander was, but i will talk in english

The OLEobject also doesn't work

I don't understand it, it won't work. It shouldn't be so hard i think im looking over something. So I will just explain what i wan't to do.

I have 10 command buttons on 10 different pages ( using the multipage )
On every page you have some textboxes

If i press a command button the data in the textboxes will be processed and put into the sheet, this works perfectly. ( not efficient but working on it )

Now i need a function or sub to add the date, week, month and year in the sheet in different cells.

How do i do this ? And where do i put what ?

 

[WinteE]

Hoi,

I've just tested the code below. Works like a charm.

Sub SetDatum()
   Dim ctrl As OLEObject
   For Each ctrl In Sheet1.OLEObjects
       If ctrl.progID Like "*CommandButton*" Then
            Range("A1").Value = ctrl.Name
       End If
   Next ctrl
End Sub

This gives me the name of the last command button in cell A1 on sheet1

Please try this code.

 

[Gowodka]

waaaah

It doesn't work for me

I opened a new sheet, made a userform, made a commandbutton name: cmdOK and caption = Test.

I putted the code into the cmdOK_click code and in a module and in the general code space. I used a Call if it was in the module without the call, when it was in general i tried with and without call but it just won't work

What do i do??

Plzzz help me.

do i have to use the OLEObjects ? isn't there a very easy simple way to use a global variable and use this in a function and use this function under a cmd_click

 

[WinteE]

Do you point to the right sheet, in my example "Sheet1" ?

 

[Gowodka]

Yes,

I made a new workbook and put is in sheet1

ik ben vrij radeloos dat zoiets simpel niet werkt :s