June 27, 2016
I created the below macro today. Going through it, if I choose xlThin or xlThick for my thickness it works fine, if I choose xlMedium it gives an "Unable to set the Weight property of the Border Class", and I can't figure out why. The macro lives in my PERSONAL, and I've been testing it on a blank sheet, so no example workbook is attached. Any help would be appreciated.
Sub BorderChooser()
'in Formatting Tools
Dim Area As String
Dim Style As String
Dim Thickness As String
Area = InputBox("All Choices work with your Entire Selection." & vbCrLf & vbCrLf & _
"Typing A creates a Border around it all." & vbCrLf & _
" C creates a Border around each Column." & vbCrLf & _
" R creates a Border around each Row." & vbCrLf & _
" U creates top and bottom borders for each row.", "How are we bordering?")
If StrPtr(Area) = 0 Then Exit Sub 'User Canceled
Style = InputBox("How Thick do you want your lines?" & vbCrLf & _
"Enter T for Thick, " & vbCrLf & _
"S for Thin(Skinny), or" & vbCrLf & _
"M for Medium.", "Choose your Line Weight")
If StrPtr(Style) = 0 Then Exit Sub 'User Canceled
Select Case UCase(Style)
Case "T"
Thickness = xlThick
Case "S"
Thickness = xlThin
Case "M"
Thickness = xlMedium
Case Else
Exit Sub
End Select
Select Case UCase(Area)
Case "A"
Selection.BorderAround Weight:=Thickness
Case "C"
With Selection
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = Thickness
.BorderAround Weight:=Thickness
End With
Case "R"
With Selection
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = Thickness
.BorderAround Weight:=Thickness
End With
Case "U"
With Selection
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = Thickness
End With
Case Else
Exit Sub
End Select
End Sub
October 5, 2010
Hi Jomili,
The variable Thickness is defined as a string but it should be a numeric e.g. Long
https://docs.microsoft.com/en-.....rderweight
Regards
Phil
Answers Post
Trusted Members
Moderators
November 1, 2018
October 5, 2010
VBA allows implicit conversion - converting one data type to another - and it handles the type conversion. So when you assigned a number to a string, VBA just dealt with it.
When it came to assigning that string value to the Borders Weight, VBA seems to have handled the conversion back from a string to a number just fine for xlThin (2) and xlThick (4). I'm concluding that is because they are positive numbers.
However xlMedium has a value of -4138 and it seems VBA hasn't converted this back from a string to a number. I don't know why this would be, I don't know how the underlying type conversion works.
If you used ActiveCell.Borders(xlInsideHorizontal).Weight = CLng(Thickness) then it would have worked as you would be explicitly converting the string to a long.
Cheers
Phil
1 Guest(s)