The code is syntactically correct, compiles and runs without a problem (for me, at least). It also produces the expected output.
Which line throws the error for you?
The code is syntactically correct, compiles and runs without a problem (for me, at least). It also produces the expected output.
Which line throws the error for you?
Would have helped to have all the information first ...
Will have to leave it for someone else as I'm out of time.
To save time ...
The nursing station is listed as 'ABU' but is 'AUB' in the output. Intentional or a mistake?
Have you tried the CONCATENATE() function?
=CONCATENATE(A1, B1, C1
Then there's the simple formula:
=A1 & B1 & C1
Not necessarily - it works perfectly fine as long as the linked cell is in the same workbook. If you are assigning the linked cell in code make sure you are using the correct format for the cell address:
'Sheet Name'!$A$1
The single quote marks must be used if there is a space in the sheet name.
It is possible to link to a cell in an external workbook but then both workbooks must be open or else the option buttons cannot be changed/clicked:
?selection.linkedcell
[Book2.xlsx]Sheet1!$A$1
(Note the '[' & ']' around the workbook name. No single quotes needed here as no space in sheet name)
Runtime error 1004
Member can be accessed only for groups
Then you did not 'Group' the frame and controls... see this page for some very concise instructions: https://support.microsoft.com/en-us/office/g…62-ee5ec72b1bd3
'Group 1' in that example is the Group name. This will be assigned automatically by Excel when the controls are grouped. You can manually change it to something more relevant using the Naming box which is usually displayed to the left of the Formula Edit box.
Am assuming these are 'Form' controls placed directly on the worksheet...
They're not the most elegant of things but useful for quick hacks (I guess). One particular disadvantage is the group box is simply a pretty frame, any controls drawn inside the frame are not treated as children of the frame but of the worksheet which makes it a PITA to determine which controls are in (for want of a better word) which frame.
To get around this limitation I might suggest you group a frame and all its controls - Click/Shift-click to select then use the 'Group' option in the right click context menu to create the group.
Once each frame has been grouped with its controls you can use code like below to show/hide the option buttons. This is a just a generic example, you still need to identify which group to process and change the procedure to suit (As written it just processes a group called 'Group 1')
It wouldn't have taken too long to find multiple examples around the place ...
Sub x()
Dim i As Integer
On Error GoTo Handler
With Selection
i = Application.InputBox("Number of cells to insert...?", "Input", .Columns.Count, Type:=1)
.Resize(.Rows.Count, i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Exit Sub
Handler:
MsgBox "Unable to insert cells." & vbCrLf & vbCrLf & "Error: " & Err.Description & " (" & CStr(Err.Number) & ")", vbExclamation
End Sub
Display More
Not sure about the 'copy' comment as formulas cannot 'copy' text - they simply display the result of a calculation...
Simplest is
=IF(A3<>"", J2, "")
Excel is basically paused while editing a cell - no VBA code will run and formulas will not recalc.
So you can consider this a non-starter.
Like I said ..."Typed freehand - Substitute in your range, value to find and text to insert if he value exists".
Was on a phone, no Excel, so took the easy way out and just typed a generic formula
You could use COUNTIF() in G11
=IF(COUNTIF(Sheet2!A4:H9, Sheet1!B5)>0, "Found", "")
(Typed freehand - Substitute in your range, value to find and text to insert if he value exists).
The general consensus on merged cell is 'avoid unless absolutely necessary' - they screw up so many things. The thought of 100+ cells merged is the stuff of nightmares.
A somewhat reasonable alternative to merging is 'Center across selection' (Format/Alignment/Horizontal) which may help but that depends on exactly what you are doing. One of those times a sample workbook (anonymised) would help.
Try using the cell TEXT property rather than the VALUE. This returns the formatted text as displayed in the cell rather than the underlying value. If is still errors then post a sample workbook, suitably anonymised if necessary.
There is nowhere enough information to be able to give a specific answer so a very general demo showing one way to get data from an external workbook.
This uses SQL to extract the contents of sheet1 from a file. You can browse to select which file. It expects headers in Row 1 but, other than that, will import all the data from sheet1.
The 'Test' workbook contains random data - the Import workbook has a button on sheet1 to select a file.
Needless to say it can be refined/extended but given the lack of detail in your post...
Unintended consequences... The 'CurrentRegion.Clear' statement also cancels any pending Paste operation.
Change the line Sheet2.Range("C13").CurrentRegion.Clear
to
Sheet2.Range("C13").CurrentRegion.Value = vbNullString
Attachment displays a button if the selected cell has comments - use to copy to sheet 2.
The code does not use any Copy/Paste functionality so that's a bit of a mystery. Can you upload a sample workbook (suitably anonymised, if necessary) that displays the issue?
It can be started any which way you want - but sort the copy/paste problem first.
This goes in the code module of the sheet containing the comments to copy...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim vComments As Variant
On Error GoTo Catch
'// Caveats:
'// 1. There cannot be any blank lines in the comments.
'// data after a blank line may not be cleared when the next cell is selected
'// 2. There must be a clear region all around the comments.
'// If there is data in B13 then that and adjacent cells
'// will also be cleared.
Sheet2.Range("C13").CurrentRegion.Clear
'// Only process if 1 cell selected
If Target.Cells.Count = 1 Then
If Len(Target.Comment.Text) > 1 Then
Select Case True
Case InStr(Target.Comment.Text, vbCrLf) > 0
vComments = Split(Target.Comment.Text, vbCrLf)
Case InStr(Target.Comment.Text, vbCr) > 0
vComments = Split(Target.Comment.Text, vbCr)
Case InStr(Target.Comment.Text, vbLf) > 0
vComments = Split(Target.Comment.Text, vbLf)
Case Else
'// Unexpected - jump to error handler, but simply ignore
Err.Raise 30001
End Select
'// All in 1 cell
' Sheet2.Range("C13").Value = Application.Transpose(vComments)
'Sheet2.Range("C13").Resize(UBound(vComments) + 1).Value = vComments
'// Update Individual cells - probably simpler to understand
Dim vComment As Variant
Dim Counter As Long
For vComment = LBound(vComments) To UBound(vComments)
Sheet2.Range("C13").Offset(Counter).Value = vComments(vComment)
Counter = Counter + 1
Next
End If
End If
Catch:
End Sub
Display More