Posts by xsmurf

[USER="33159"]KjBox[/USER] Thanks for your answer.
So Im guessing I can't do it without using cells. I have to have a reference from a sheet.

Again thank you.

Hello,

I was wondering if something was possible.

If I make a list on a sheet in cel B3 through the option " Data Validation", and fill it for example with the options "test;test1;test2"
would it be possible to make userform that reads the list options in cell B3, show them in the userform and be able to add or remove an option?
So if you want to remove the option "test2" you can do that from within the userform, same if you want to add a option.

I know you can have a reference to a range of cells, but I want to know if it is possible?

Thank you.

Hello,

I have a question, because I have a problem regarding a VBA code

I found a code that does only partially what I want.
I'm trying to highlight dates that are equal or lower then the current date (this date is on a different sheet, called Setup and the date is in cell "F3")
If there is no date in the range given no formatting should take place.
And only the range given should be highlighted, and not the rows in between the range.

Can somebody assist me with this problem, it would be highly appreciated.

The Code:

Sub ChangeColor()
    Dim myDate As Date
    Dim rngCell As Range
    Dim lrow  As Long
     'format the date excluding time
    myDate = FormatDateTime(Now, 2)


    For Each rngCell In Range("C3:BB3", "C27:BB27")


   Select Case DateDiff("d", FormatDateTime(rngCell.Value, 2), myDate)
        Case Is >= 0
        rngCell.Interior.ColorIndex = 44
        rngCell.Font.ColorIndex = 55
        Case Is = Empty
        rngCell.Interior.ColorIndex = 48
        rngCell.Font.ColorIndex = 58
   End Select
    Next


    Exit Sub


End Sub

Re: Copy values from one workbook to another with merged cells

jolivanes
I mixed the codes ( the one I aleady was using, and the one you provided) and it got the whole thing working. Again thank you for your help.
Happy holidays

Re: Copy values from one workbook to another with merged cells

jolivanes
sorry for the late response, just came back from a little trip.
But that looks indeed in what i was looking for, thank you so much.
I will try to adapt it to my worksheets, and hopefully my problems are gone.
If not, I will be back :wink:

Re: Copy values from one workbook to another with merged cells

jolivanes
Thanks for a possible solution, but where do I put this code in. Will that be instead of the arrays I use or is this a extra piece of code that I need.

Re: Copy values from one workbook to another with merged cells

KjBox
as requested
The main.xls file is where I collect the data, the test file is where I put the data in

forum.ozgrid.com/index.php?attachment/70905/ forum.ozgrid.com/index.php?attachment/70906/

Re: Copy values from one workbook to another with merged cells

KjBox
just tried it, but that doesn't work. Still get the "runtime error 13 - Type mismatch". It also doesn't makes the row heiger to fit the text, its stays at the same height.

Hi,

I have a little problem with a code i'm using.

I have 6 workbooks and one main workbook.
The 6 workbooks are filled in by 6 different people, this is nto a problem.
The main workbook is opened when a meeting starts, and with a click on a button all the info from the 6 other workbooks are put in the main sheet. This works pretty good, but with a few flaws.

The cells that people fill in those 6 workbooks are merged cell ( for example G5:AA5 ), and that data is being copied to the main workbook also in merged cells ( for example G32:AA32 )
All goes wel as long people stay within the width of the merge cells, if they go outside the width of the merge cells i get an error in the code. That happens also when they use ALT+Enter.

I know working with merged cells is not the best option within excel, but can't yet find a different solution.

The fix i like to have : copy the data in the main workbook, in the merged cells, without problems if the cell value is bigger then the merged cells.
Hope somebody can help me.

The codes I use are also found on boards, so credits goes to those people.

The code for collecting the data:

Option Private Module


Private Function GetValue(Path, File, Sheet, ref)
'Deze functie haalt data op uit een gesloten of geopend werkboek




Dim arg As String
    If Right(Path, 1) <> "\" Then Path = Path & "\"
        If Dir(Path & File) = "" Then
                GetValue = "File not found"
            Exit Function
    End If
            arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
            Range(ref).Range("a1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

The code in a module to get the data from one workbook: the row giving a problem is commented

Public Sub OphalenTekst_wg1()Dim Path As String, File As String, Sheet As String, TargetSheet As String, CheckValue As String, a As String
Dim i As Integer
Dim SourceArrays As Variant
Dim TargetArrays As Variant




Path = "Q:\Test\Meeting"
File = "WG1.xls"
Sheet = "WG1"
TargetSheet = "Meeting"
SourceArrays = Array("G2", "M2", "C5", "G5", "G7", "C10", "C11", "C12", "D10", "D11", "D12", "E10", "E11", "E12", "E13", "F10", "F11", "F12", "G10", "G11", "G12", "G13", "D14" _
    , "B18", "B19", "C18", "C19", "E18", "E19", "G18", "G19", "H18", "H19", "J18", "J19", "L18", "L19", "M18", "M19", "O18", "O19", "Q18", "Q19", "R18", "R19", "T18", "T19", "V18")
                    
TargetArrays = Array("N29", "A37", "C32", "G32", "G33", "C34", "C35", "C36", "D34", "D35", "D36", "E34", "E35", "E36", "E37", "F34", "F35", "F36", "G34", "G35", "G36", "G37", "B38" _
    , "B40", "B41", "C40", "C41", "E40", "E41", "G40", "G41", "H40", "H41", "J40", "J41", "L40", "L41", "M40", "M41", "O40", "O41", "Q40", "Q41", "R40", "R41", "T40", "T41", "V40")
Worksheets(TargetSheet).Select
            
            For i = LBound(SourceArrays) To UBound(TargetArrays)
                a = Range(SourceArrays(i)).Address
               CheckValue = GetValue(Path, File, Sheet, a) '*****ERROR ON THIS LINE******
               If Len(CheckValue) = 1 And CheckValue = "0" Then CheckValue = Empty
                Range(TargetArrays(i)).Value = CheckValue
            Next i
 
End Sub

Thanks for the assist.