Posts by sirbeetee

I have this file wich opens with a Userform. it's been in use for 2 years now and works fine.

now I have 200 users that will have acces to it. I have noticed that if more than 1 usuer has it open, the will be prompt only when they "save" that they are in "read only", and this after imputting possibly over 100 lines of data

usualy when you open a file that is already openned by an other user you get a prompt immidiatly. why am I not getting one now ?

my file is too large to insert here sorry

Hello gang

Have a userform that works very well

so well that I've been asked to upgrade it

in a userform, I pesently have 2 option buttons, one has to be selected to continue. but now, I must add:

if OptionButton_Imcomplet=true then at least 1 of the 18 checkboxs must be checked

tried this but stops even if one checkbox is selected

ElseIf OptionButton_imcomplet = True Then
   Dim cb As Shape
 Dim valid As Boolean
 valid = False
 For Each cb In ActiveSheet.Shapes
 If cb.Type = msoFormControl Then
 If cb.FormControlType = xlCheckBox Then
 If cb.ControlFormat.Value = xlOn Then
 valid = True
 Exit For
 End If
 End If
 End If
 Next cb
 If valid = False Then
 MsgBox "Pour un bulletin de travail invalide, vous devez identifier au moins une anomalie"
 Range("B3").Select
 Exit Sub
 End If

Re: code to format cells according to variables

you're right ! works just fine !

thanks alot !!!

hello from Montréal

I have a Worksheet in wich I have 5 différents variables (in cells E61 to E65)

I would like VBA to recognise the value in those cells and format le nomber of cells coresponding in different lines

say E61 value is 10

I want VBA to format the next 10 cells with borders, for all lines with "10" in colombs "B", starting in colomb "G"

the attached file should be more explicit, you have the blanck sheet and the result wanted sheet

thanks in advance for your help

Re: how to code a variable to send value from cells to specific sheet according to va

learn

noted !

Re: how to code a variable to send value from cells to specific sheet according to va

Just tried it out, EXCELLENT !

merci cytop

love it when I lerne something new !

Re: how to code a variable to send value from cells to specific sheet according to va

actually their is no logic for changing the sheet name, it's just that I don't know what the exact values will be. but no worry, it's realy not important, more of an exercise for me

again Cytop, thanks for your help

I'll try it out today !

forum.ozgrid.com/index.php?attachment/67627/hi gang

I have coded before, I can make a code take data from cells, and with a button sent it to a different sheet.

now I need to do the same, but depending on the value in the first cel I want the data to go to a coresponding sheet

exemple:

sheet Data, cell b1 to b5 has data.
in cell b1 is a list of 4 different possibilities. if possibilité 1 is celected, I want the data from all the cells to go to a specific sheet, etc

at the moment, my code sends every thing to the same sheet, so I need to understand how to create a variable

also, the sheets have code to rename the sheet according to info il the last sheet

Re: Find next emply cel in row if row is = condition

this file is then printed and posted for consultation, wich explain the look, but thanks for trying to help, I'll keep serching

Re: Find next emply cel in row if row is = condition

forum.ozgrid.com/index.php?attachment/67467/ok, let me explain it better

in week one, 48 employes may be on vacation
in week 2, 48
in the summer it's 87 employes per week

so it's always 52 weeks, but all in all I have actually over 2400 vacation weeks to give in the year

each employe may chose there first 2 weeks, but must wait till every one has chosen their first 2 weeks. if they chose outside the summer periode they may chose more than 2 weeks. that's way the code "black" for the first 2 picks does'nt work for me

also, I have added a value at the end of each week that serve has a stopper, so that I cant book more in that given week, but the code does'nt work now

then, as some employes have 2, 3, up tu 7 weeks vacations time, they may chose the reste of their vacation weeks, and I want those in red.

Re: Find next emply cel in row if row is = condition

thanks cytop

yes it would be simpler but since my real grid has 1700 weeks, it becomes easyer with the color coding

Re: Find next emply cel in row if row is = condition

cytop, one more question

now that it's running ok, got an idea

I'd like a second button that does exactly the same thing, but colors the value returned red

"Why ? well they all start with chosing 2 weeks and once every one has done that, they can then chose the reste of there vacation and I want to see wich weeks were chosen on the first pass in black and the rest in red"

suggestions ?

Re: Find next emply cel in row if row is = condition

SOLVED ! Thanks alot cytop !!!!

RÉSOLU ! Merci beaucoup cytop !!!!

Re: Find next emply cel in row if row is = condition

wow that was quick !

almost there, but if employe choses week # 1, it goes to week 10, not 1, as if "1" is read as "10" in cells B2 to H2

First post ever for me !

I have found answers here before, but this time I need to ask a specific question

I'm in Montréal, french Montréal so excuse my wording

I have a few hundred employes who have each between 2 to 7 weeks vacation time

I have a grid in Excel wich identifies how many employe may be on vacation on a given week

I want to enter in A1 the employe number, then in B1 the number of his first vacation week, in C1 the number of his second vacation week etc

I then have a button wich I want the code to take the employe number and copy it in the next empty cel of the row coresponding to his vacation week number

attached is a file "with no code at the moment" wich represents what I want it to look like

now I have coded before, but just don't know how to send the value of A1 in the next emply cel of the correct row

in the attached file, "44444" is in A1, and should be sent to cells D6, F7, E8, E9, D10 and C11, according to his choices made in cells B2 to H2