PDA

View Full Version : Sleeper: VBA Code error for If then else.



falcon
07-08-2005, 05:04 PM
The code below copies and pastes data to and from different tabs in a Excel spreadsheet. The 2 tabs are "MASTER" & "ON_OFF" I dont if you can follow my weak attempt to write code but it always gets an error, if its not a IF, END or With error. Any help would be appreciated.


Private Sub ToggleButton1_Click()
If ToggleButton1.Value = "False" Then
Sheets("ON_OFF").Select
With Range("M59").Select
Selection.Copy
Sheets("MASTER").Select
Range("K59").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("H59").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I59").Select
Application.CutCopyMode = False
Selection.Copy
Range("G59").Select
ActiveSheet.Paste
Range("I59").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D59").Select
End With
Else
If ToggleButton1.Value = "True" Then
Sheets("MASTER").Select
With Range("G59").Select
Selection.Copy
Range("I59").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G59").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("ON_OFF").Select
With Range("P59").Select
'--------- Selection.Copy
Sheets("MASTER").Select
Range("K59").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H59").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D59").Select
End If
End With
End Sub

In advance - Thanks for helping. :bow:

BlueCactus
07-08-2005, 05:16 PM
Welcome to VBAX, falcon.

I added VBA tags to your post and deleted the font tags. I hope it came out correct. If so, I think some of the problems will be more apparent now. You can see some mismatched block endings at the end of the code.

johnske
07-08-2005, 05:36 PM
Welcome to VBAX, falcon.

I added VBA tags to your post and deleted the font tags. I hope it came out correct. If so, I think some of the problems will be more apparent now. You can see some mismatched block endings at the end of the code.Yes, at a glance, you're missing an 'End If'

HTH,
John

Bob Phillips
07-08-2005, 05:39 PM
Yes, at a glance, you're missing an 'End If'


And an End With

rbrhodes
07-08-2005, 07:28 PM
Hi,

The last lines should be:



Range("D59").Select
End With
End With
End If
End If
End Sub


Cheers,

dr

johnske
07-08-2005, 10:22 PM
Hi falcon,

I've had more time to look at this... You appear to only want the values from individual cells copied and pasted. If it's only a few values, there's no need to copy and paste at all.

If I've interpreted what you're trying to do correctly, this should work


Option Explicit
Private Sub ToggleButton1_Click()
Dim MainSheet As Worksheet
Set MainSheet = Sheets("MASTER")
With MainSheet
If ToggleButton1.Value = False Then
.Range("G59") = .Range("I59")
.Range("I59").ClearContents
.Range("H59, K59") = [ON_OFF!M59]
ToggleButton1.Caption = "True"
Else
.Range("I59") = .Range("G59")
.Range("G59, H59").ClearContents
.Range("K59") = [ON_OFF!P59]
ToggleButton1.Caption = "False"
End If
End With
MainSheet.Activate
MainSheet.Range("D59").Select
Set MainSheet = Nothing
End Sub

HTH,
John